SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

[SQLSTATE 23000] (Error 547) The statement has been terminated. [SQLSTATE 01000] (Error 3621)

SQL Agent job failed with below Error

Executed as user: ArrivalsDepartures. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DepartureStatusArchive_TechnicianName". The conflict occurred in database "DatabaseName", table "dbo.Users", column 'UserID'. [SQLSTATE 23000] (Error 547)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

Root Cause: Error is not refering the table which is having Foreign-key to the Users table rather it mentioned the constraint name FK_DepartureStatusArchive_TechnicianName.
Also the procedure is inserting data from Table A to Table B. Table B is having the foreign key to Users Table. This foreign key column is accepting NULLs but the column in Users table is Primary Key.

Fix: Actually the culprit table is Archive table and the table structure was changed recently as part of one change. As the table is Archive and it not required referential key to Users table I dropped the foreign key and now the job executed successfully.

Dropped Foreign Key: Expand the table B->expand the Keys->right click and script the key to be deleted-> run it against the Database and the constraint is deleted.

SQL Articles by Nani

Finished Masters in Computer applications and started the career with SQL application and DBA support. Have experience working with different versions of SQL starting from SQL 2000 to 2014. Also worked on SSRS and SSIS. Currently working in US based company as SQL DBA and Developer. I am very much interested in documenting SQL Server information and doing it everywhere as required. I love learning, working and documenting SQL Server.


Leave a comment on the original post [sqlloverforever.blogspot.com, opens in a new window]

Loading comments...