SQL Agent job failed with below Error
Message
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.
Message
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.
No comments:
Post a Comment