In an upgraded CA SDM 14.1 environment, running "pdm_load -r -f tucson.dat" to delete a row in ca_state_province fails with: The DELETE statement conflicted with the REFERENCE constraint "ca_location_fk04".

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• KNOWLEDGE TOOLS
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

The post-installation instructions for cumulative patch #3 (14.1.03) for CA Service Desk Manager (CA SDM) 14.1 includes a step to run the command, "pdm_load -r -f tucson_delete.dat". 

When the environment has been upgraded from CA SDM 12.6 to CA SDM 14.1, the command may fail and the following block of messages would be written to the stdlog:

12/06 14:23:29.64 sdmsrv001     sql_agent 3636 SIGNIFICANT sql_agent.c 245 STARTUP of sql_agent:mdb:dbload-#932:
12/06 14:23:29.97 sdmsrv001     sql_agent 3636 ERROR sqlclass.c 1010 SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=547 SQL State=23000] The DELETE statement conflicted with the REFERENCE constraint "ca_location_fk04". The conflict occurred in database "mdb", table "dbo.ca_location", column 'state'.
12/06 14:23:29.98 sdmsrv001     sql_agent 3636 ERROR sqlclass.c 1011 Clause (DELETE FROM ca_state_province WHERE id = ?) Input (<id:int>1)
12/06 14:23:29.98 sdmsrv001     sql_agent 3636 ERROR sqlsrvr.c 651 Execution failed. MISC_DB_ERROR
12/06 14:23:29.98 sdmsrv001     dbload 932 ERROR dbintf.c 745 Db callback event:6 row:1 error:15
12/06 14:23:29.98 sdmsrv001     dbload 932 ERROR dbintf.c 761 SELECT #creation_user , #last_update_user , #version_number , #inactive , #id FROM ca_state_province
12/06 14:23:29.98 sdmsrv001     dbload 932 ERROR dbintf.c 757 Db callback event:5 error:1
12/06 14:23:29.99 sdmsrv001     dbload 932 ERROR dbintf.c 761 SELECT #creation_user , #last_update_user , #version_number , #inactive , #id FROM ca_state_province
12/06 14:23:30.99 sdmsrv001     dbload 932 SIGNIFICANT dbload.c 766 Dbload Completed

 

Cause

The foreign key constraint "ca_location_fk04", which is defined for the table "ca_location", is preventing the deletion of a specified row that exists in the ca_state_province table.  This is because one or more rows in ca_location reference the row in ca_state_province.

For example, for SQL Server, the Key, ca_location_fk04, is defined according to the following SQL commands:

ALTER TABLE [dbo].[ca_location] WITH CHECK ADD CONSTRAINT [ca_location_fk04] FOREIGN KEY([state])
REFERENCES [dbo].[ca_state_province] ([id])

ALTER TABLE [dbo].[ca_location] CHECK CONSTRAINT [ca_location_fk04]

The row that is attempted to be deleted is specified in the "tucson_delete.dat".  That file contains:

#This migration file is to delete itil and non itil data.

###############################################################################
TABLE ca_state_province
   id creation_user last_update_user version_number
   { "1", "acme", "acme", "1" }

 

In the mdb database, the row in ca_state_province would be similar to the following:

id	inactive	symbol	creation_user	creation_date	last_update_user	last_update_date	version_number	description	exclude_registration	delete_time	tenant
1	0	null	acme	1081787612	acme	1081787612	1	null	NULL	NULL	NULL

Environment

CA Service Desk Manager 14.1, which was upgraded from a previous version of CA SDM.

Resolution

1. Take a database backup of the ca_location and the ca_state_province tables of the mdb database.

2. Determine the Locations which are referencing the State/Province that matches the entry that the pdm_load command is attempting to delete. 

For example, in a Windows environment, you could run the following  command:

pdm_extract -f "select id, location_name, state from ca_location where state=1" > ca_location_orig.out

3. If there are only a few locations, update those via the CA SDM web browser GUI by editing the location and updating the State/Province field to a different value. 

Alternatively, for a bulk update, you could update the rows in the ca_location table that are returned in the previous step using pdm_load.

Important! Be sure that you understand the risks of using pdm_load before you use it. See Additional Information section of this technical document. 

To use pdm_load to make the change, you could perform the following steps:

For example, on Windows, first run this command:  copy ca_location_orig.out ca_location_new.out

Then, edit the file, ca_location_new.out, and, for each row, change the "1" that is in the third column to "". 

For example, for the location "loc1" entry, change:
 { "18503A79B9C1614784F920514E30A71C" ,"loc1" ,"1" }
to:
 { "18503A79B9C1614784F920514E30A71C" ,"loc1" ,"" }

Next, run pdm_load to load the NULL value into the "state" column of the corresponding rows in the location table using the following command: pdm_load -a -f  ca_location_new.dat 

4. Re-run the post-installation step, in particular, the re-run command: pdm_load -r -f tucson_delete.dat

Additional Information

The post-installation steps should guide you as to the location of the file, "tucson_delete.dat" When you run the pdm_load command as shows in this technical document, the current directory must contain the file.

Before you run pdm_load, please review the "Important!" note that is included on the following page: pdm_load--Add, Update, and Delete Database Records