Posted by vikram.mankar on 02/05/07 21:47

Nevermind...

added "SET NOCOUNT ON" to the Sp and the task now works just fine.

On Feb 5, 3:14 pm, vikram.man...@gmail.com wrote:
> 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]