|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
running stored procedures from SQL Agentas 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 Well, why not re-write the INSERT statement so that duplicate keys are not > 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. 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 > inserted at all? Or, better yet, why bother having a primary key at all Of course, it's late on Friday, and my brain is fried. Some of this stuff > if you are just going to insert redundant data and ignore the duplicate? I'm saying today doesn't make sense to my dogs, never mind me. Thanks for the response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message I think that may be my only choice. I was hoping to avoid this as there are news:OzX0Rj49FHA.2936@tk2msftngp13.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. > > 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? 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. > Yes. This is really a band-aid for some older code that created indexes with > 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. this option. Unfortunately these are on what are probably the biggest tables in the database. Thanks again for the advice, -Gary > I fear that all the speed I've gained by doing BULK INSERT (over the Well, don't you think SQL Server is doing all that work anyway? How else > 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. 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 Is it only my posts that are coming in pairs?
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eqN3K849FHA.3608@TK2MSFTNGP09.phx.gbl... >> 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 > That works. Thank You!
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eqN3K849FHA.3608@TK2MSFTNGP09.phx.gbl... >> 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 Well, don't you think SQL Server is doing all that work anyway? How else > 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. 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 BTW, what version are you at? There was a hotfix for this issue a while
back: http://support.microsoft.com/?id=295032 Show quote "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 > > > 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 Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message 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've got SP3. I did see that there was a bug fixed in SP1 related to this. Right, but there is more to SQL Agent than just calling your code... this is > What really surprises me is that it behaves differently when run as a job > step. All I do is "exec <sp>". 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. A That's pretty much the answer I was looking for.
Thanks again. -Gary Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:O$ZMc949FHA.2320@TK2MSFTNGP11.phx.gbl... >> 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. > > A > BTW, what version are you at? There was a hotfix for this issue a while
back: http://support.microsoft.com/?id=295032 Show quote "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 > > >  |
|||||||||||||||||||||||
Other interesting topics