running stored procedures from SQL Agent

I'm trying to understand some behavior I'm seeing running a stored procedure
as a TSQL jobstep in a SQL Agent job. If I run the sp from query analyzer or
osql, it completes as expected, but in the process may generate a few 3604
errors (duplicate keys ignored on insert). If any errors other than 3604 are
generated, the procedure aborts.
When running this as a job step, this 3604 error causes the step to fail,
which I can understand. To try to work around this problem, in this stored
procedure I write out the status codes to a table each time. If the job
reports failure, I set the next job step to search this table of status
codes for any that are non-zero and not 3604. If none are found, this
jobstep reports success, and the job completes with success, otherwise the
job fails. The problem is, the first job stop does not actually run to
completion; it process a few times through its main loop and then seems to
just abort. Looking at the generated log file I see no indication of errors.
Profiler tells me that the first job step completed, but I can tell from the
logfile that it did not. It then goes onto run the second step, which
completes with success.
Any advice as to why this is happening would be greatly appreciated. I know
that error handling is somewhat limited in T-SQL, and that I cannot suppress
the error completely. I have a fair amount of experience working with SQL
Agent and jobs and have never run into a problem like this where a procedure
behaves differently when run as a job step.
Thanks for any advice.
-Gary> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
Well, why not re-write the INSERT statement so that duplicate keys are not
inserted at all? Or, better yet, why bother having a primary key at all if
you are just going to insert redundant data and ignore the duplicate?
I imagine the main problem is that data is coming from BCP or BULK INSERT.
My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you insert
into a work table and then perform an insert/update combination on the
primary table, after you've cleaned up the data.
Back to the original problem, while msg 3604 is technically a warning and
not an error, some applications are still going to view it as an error, and
there is not much you can do about that...
A|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
Of course, it's late on Friday, and my brain is fried. Some of this stuff
I'm saying today doesn't make sense to my dogs, never mind me.|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>|||Thanks for the response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OzX0Rj49FHA.2936@.tk2msftngp13.phx.gbl...
> Well, why not re-write the INSERT statement so that duplicate keys are not
> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
I think that may be my only choice. I was hoping to avoid this as there are
existing tables that are very large and I fear that all the speed I've
gained by doing BULK INSERT (over the previous way, which was done in vb,
but at least the warning could be suppressed) will be lost having to search
the existing data for anything in the work table. But I suppose that's what
indexes are for. I'm already creating a temp table to store the data before
it gets to it's final destination table, maybe it won't be too bad.

> I imagine the main problem is that data is coming from BCP or BULK INSERT.
> My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you
> insert into a work table and then perform an insert/update combination on
> the primary table, after you've cleaned up the data.
Yes. This is really a band-aid for some older code that created indexes with
this option. Unfortunately these are on what are probably the biggest tables
in the database.
Thanks again for the advice,
-Gary|||I've got SP3. I did see that there was a bug fixed in SP1 related to this.
What really surprises me is that it behaves differently when run as a job
step. All I do is "exec <sp>".
Thanks,
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eNhK3n49FHA.1484@.tk2msftngp13.phx.gbl...
> BTW, what version are you at? There was a hotfix for this issue a while
> back:
> http://support.microsoft.com/?id=295032
>|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes|||> I've got SP3. I did see that there was a bug fixed in SP1 related to this.
> What really surprises me is that it behaves differently when run as a job
> step. All I do is "exec <sp>".
Right, but there is more to SQL Agent than just calling your code... this is
NOT apples to apples. For starters, it uses a different library to connect
to the server than, say, QA or VB would. It also executes as a different
user, in most cases, unless your job is owned by SA and you routinely
connect via QA or VB using SA (shame, shame).
I am sure there are long-winded discussions on google and probably some good
information in Books Online about all the nitty-gritty differences between
code you execute and code SQL Agent executes. The short answer is that they
are not the same.
Asql