Stored procedure and SQL Job Agent Task

[vikram.mankar]

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?

[vikram.mankar]

Nevermind...

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

On Feb 5, 3:14 pm, [e-mail] 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?

[Erland Sommarskog]

(vi***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[vikram.mankar]

Would NOT EXISTS add any overhead to the query? The application
logging data to the SQL server is using OLEDB and as a test is
currently logging nearly 10000 rows in the table every minute. I was
wondering if NOT EXISTS would actually slow down the INSERT.

[Erland Sommarskog]

(vi***********@gmail.com) writes:

Would NOT EXISTS add any overhead to the query? The application
logging data to the SQL server is using OLEDB and as a test is
currently logging nearly 10000 rows in the table every minute. I was
wondering if NOT EXISTS would actually slow down the INSERT.

I am a little confused here. In your original post you talked about an
Agent job and you even mentioned a cursor. Now you are talking about
an application that logs data.

But let's say that we are talking about application logging now. Then I
am curious: how come the application is (attempting to) logging duplicates?

As for the question as such, NOT EXISTS may add an overhead, but so will
an error that has to be rolled back.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[vikram.mankar]

The application logs "raw" data. The SQL Job (stored procedure) is
adding attributes to that data and moving it to "report" tables. The
cursor was used to select the right attributes based on the data
logged.

The application is logging data from a hardware device (PLC) thats
generating data faster than the application can accept (at times) ..
hence the issue of duplicates to avoid any data loss during the timing
issue. There is unfortunately no way to control the duplicate problem
at the application level. But since its generating data so fast - we
need to just dump the raw data in a table and then copy it for
reporting purposes.
On Feb 6, 5:32 pm, Erland Sommarskog <[e-mail]:

([e-mail]) writes:

Would NOT EXISTS add any overhead to the query? The application
logging data to the SQL server is using OLEDB and as a test is
currently logging nearly 10000 rows in the table every minute. I was
wondering if NOT EXISTS would actually slow down the INSERT.

I am a little confused here. In your original post you talked about an
Agent job and you even mentioned a cursor. Now you are talking about
an application that logs data.

But let's say that we are talking about application logging now. Then I
am curious: how come the application is (attempting to) logging duplicates?

As for the question as such, NOT EXISTS may add an overhead, but so will
an error that has to be rolled back.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

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

[Erland Sommarskog]

(vi***********@gmail.com) writes:

The application logs "raw" data. The SQL Job (stored procedure) is
adding attributes to that data and moving it to "report" tables. The
cursor was used to select the right attributes based on the data
logged.

The application is logging data from a hardware device (PLC) thats
generating data faster than the application can accept (at times) ..
hence the issue of duplicates to avoid any data loss during the timing
issue. There is unfortunately no way to control the duplicate problem
at the application level. But since its generating data so fast - we
need to just dump the raw data in a table and then copy it for
reporting purposes.

I can understand that you log everthing in the raw tables. That
certainly seems like the best strategy. And my suggestion was not
that you have the WHERE NOT EXISTS in this place, but rather the in
the Agent job.

Whether the WHERE NOT EXISTS would be too costly in the Agent job,
I don't think so. After all, if you can afford having a cursor, then
you don't appear to be in a hurry...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[vikram.mankar]

I'll give it a shot. Is it generally more efficient to check for
duplicates through T-SQL like with WHERE NOT EXISTS? or use
constraints on the table? I realize the latter is a little painful as
it clogs the error logs for the job history.

thanks, Vikram

On Feb 6, 6:05 pm, Erland Sommarskog <[e-mail]:

([e-mail]) writes:

The application logs "raw" data. The SQL Job (stored procedure) is
adding attributes to that data and moving it to "report" tables. The
cursor was used to select the right attributes based on the data
logged.

The application is logging data from a hardware device (PLC) thats
generating data faster than the application can accept (at times) ..
hence the issue of duplicates to avoid any data loss during the timing
issue. There is unfortunately no way to control the duplicate problem
at the application level. But since its generating data so fast - we
need to just dump the raw data in a table and then copy it for
reporting purposes.

I can understand that you log everthing in the raw tables. That
certainly seems like the best strategy. And my suggestion was not
that you have the WHERE NOT EXISTS in this place, but rather the in
the Agent job.

Whether the WHERE NOT EXISTS would be too costly in the Agent job,
I don't think so. After all, if you can afford having a cursor, then
you don't appear to be in a hurry...

--
Erland Sommarskog, SQL Server MVP, [e-mail]

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

[Erland Sommarskog]

(vi***********@gmail.com) writes:

I'll give it a shot. Is it generally more efficient to check for
duplicates through T-SQL like with WHERE NOT EXISTS? or use
constraints on the table? I realize the latter is a little painful as
it clogs the error logs for the job history.

If you want to prevent duplicates at all, you should use constraints.
And then you may use WHERE NOT EXISTS to avoid the errors to happen.
Note that if you only use WHERE NOT EXISTS, but does not have any index
at all, performance will be awful.

As for that log table, I would not put any constraints on that table
that prevents duplicates, nor any WHERE NOT EXISTS. It seems to me that
it may be better to permitt the application to log what it logs and
weed out duplicates later.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[vikram.mankar]

Thanks Erland - i noticed the performance is much better with WHERE
NOT EXISTS... and the SQL Job completes much more cleanly.
On Feb 7, 3:20 am, Erland Sommarskog <[e-mail]:

([e-mail]) writes:

I'll give it a shot. Is it generally more efficient to check for
duplicates through T-SQLlike with WHERE NOT EXISTS? or use
constraints on the table? I realize the latter is a little painful as
it clogs the error logs for the job history.

If you want to prevent duplicates at all, you should use constraints.
And then you may use WHERE NOT EXISTS to avoid the errors to happen.
Note that if you only use WHERE NOT EXISTS, but does not have any index
at all, performance will be awful.

As for that log table, I would not put any constraints on that table
that prevents duplicates, nor any WHERE NOT EXISTS. It seems to me that
it may be better to permitt the application to log what it logs and
weed out duplicates later.

--
Erland Sommarskog,SQLServer MVP, [e-mail]

Books Online forSQLServer 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx