Interview Question in Data Management
Interview Question :: SP Failing when set as Job Step
I have a SP that basically copies data from one table to another.
Some of the data could be duplicates and so the SP detects any primary key violations, error 2627 and if detected uses a random number for the PK and tries the insert again.
This SP works fine when run manually from Management Studio but when scheduled as a job step, it fails. From investigation, it seems that the logic to handle PK violations is being processed but if there are more than around 16 PK violations in the batch copy, the job step fails at around the 17th violation insert and fails to process the rest of the step.
When this happens, as well as seeing the 2627 error logged in the message field of the job log history, it also records an error code 3621 in the SQL Message ID field of the log with Severity 14.
It is not a data issue as subsequent reruning of the job picks up where it failed previously and continues before failing again.
Does anyone know why this SP should fail as a job?
Loading ...