February 28, 2008 at 4:34 am
I have a stored procedure thats transferring/processing data from one
table to two different tables. The destination tables have a Not Null
constraint as the source tables at times it may violate the constraint and try to insert into the destination tables, it will give the errors.
When the
stored procedure (which includes a cursor) is executed through query
analyzer, it runs fine, and reports an error everytime it sees. It moves all the values from the source to the destination tables.
However, if the same stored procedure is run as a task/job in SQL
Server Agent, the behaviour is different. The job fails when it see's
the error and ends up skipping records or terminating the procedure
all together. Eg. if there are 100 records in the source table with 10
duplicates, the stored procedure when run through Query Analyzer will
copy the 90 unique records to the destination tables but when run from
SQL-Agent, it copies just 10-15 records.
Any idea why this happens, please let me know
February 28, 2008 at 4:53 am
Hi
Whenever you job encounters a error it will quit. You can set what to do when the job fails , be default its quit the job when a failure occures. Check the job properties/Steps.
I doubt whether changing the action when a failure occurs will help achieve what you have in mind.
You are better off using SSIS for this if u know it.
"Keep Trying"
February 28, 2008 at 5:22 pm
Yes everytime your job sees and error it will stop automatically reporting failure. Since you are in 2000 create a DTS package and then schedule the package.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply