Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
planetmatt
Starting Member
2 Posts |
 Posted - 2007-10-16 : 10:53:30
|
| 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 primarykey violations (error 2627) and if detected uses a random number forthe PK and tries the insert again.This SP works fine when run manually from Management Studio but whenscheduled as a job step, it fails. From investigation, it seems thatthe logic to handle PK violations is being processed but if there aremore than around 16 PK violations in the batch copy, the job stepfails at around the 17th violation insert and fails to process therest of the step.When this happens, as well as seeing the 2627 error logged in themessage field of the job log history, it also records an error code3621 in the SQL Message ID field of the log with Severity 14.Does anyone know why this SP should fail as a job? I have checkedpermissions and also tried setting the agent login and job owner tothe same account that successfully ran the SP in Mangement Studio butthis also failed.At present the only way to get this job to run is to set the stepretry attempts to a number greater than the number of fails. Eachtime the job is rerun, it will process a certain number before failingand it only fails after processing a certain number of PK violations.This work around is fine in a test environment of a few hundredrecords but this job needs to process roughly 75,000 records and if allthese happened to be duplicates, it would require over 4500 retriesassuming its fails after every 16 records.Thanks in advance.Matt |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-16 : 11:37:44
|
| post the proc and we can take a look at it. |
 |
|
|
|
|
|
SP Failing as Job Step