Posted by vikram.mankar on 02/05/07 20:14

I have a stored procedure thats transferring/processing data from one
table to two different tables. The destination tables have a unique
value constraint as the source tables at times has duplicate records
and this will prevent the duplicates from being reported. When the
stored procedure (which includes a cursor) is executed through query
analyzer, it runs fine, and reports an error everytime it sees a
duplicate value (as expected). It moves all the unique 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?

[Back to original message]