Posted by Erland Sommarskog on 02/05/07 22:22

(vikram.mankar@gmail.com) writes:
> 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.

I see that you have resolved the issue, but generally I think it's
better to use NOT EXISTS to avoid the dup error to happen in the first
place. You mention a cursor, but if the sole purpose is to copy rows
one table to another, running a cursor is not very effective.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]