January 28, 2003 at 1:57 pm
Is the a TSQL command (set of commands) that will force the SQL Server Agent Step to fail, when run through a CMDEXEC step running OSQL?
I suppose I might be able to run an sp_update_jobstep command. Would this work? Or are the much easier approachs.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 28, 2003 at 2:11 pm
I don't think you can update a job step while is still running. Even do, I don't think SQL would execute the new job step.
Can you create the job with a step command like RaiseError?.
January 28, 2003 at 2:43 pm
You might be right on the updating of a running job, I'm not sure have not tried. Ok, I tested this, and turns out you can update a running job, but the updates don't apply to the running job, they apply next time you run the job.
Having a TSQL script like such, doesnot cause the OSQL job step to error:
select zxcvxvsdfgsdfasegfwergbetbetgsefrwefwerwer
go
IF (@@ERROR <> 0)
RAISERROR ('command failed.', 16, 1)
The output for OSQL shows theat the RAISEERROR was executed, but still the OSQL jobstep completed successfully.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 28, 2003 at 2:53 pm
Can't you just write a query to a bogus table to make the step fail? We have a job that we intentionally want to fail so that we get alerted via SQL Agent mail. So in the single job step, we select * from bogustable.
January 28, 2003 at 3:11 pm
Yes this is true a basic TSQL type step will fail when a bogus command is issues. My problem is I have a script I run. The script is run using a Operating System Command (CMDEXEC) type of step, running the OSQL exe. Even when the script contains an bogus command and causes the script to fail, OSQL always finishes successfully.
I guess another way to look at this is how do you cause OSQL to fail based on some condition in your script, like RAISERROR or your script erroring out?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 28, 2003 at 3:17 pm
Greg I found this in MSDN :
quote:
The osql utility passes everything between the parentheses ( ) to the server exactly as entered. If a stored system procedure selects a set and returns a value, only the selection is returned. The EXIT( ) statement with nothing between the parentheses executes everything preceding it in the batch and then exits with no return value.There are four EXIT formats:
EXIT
Does not execute the batch; quits immediately and returns no value.
EXIT( )
Executes the batch, and then quits and returns no value.
EXIT(query)
Executes the batch, including the query, and then quits after returning the results of the query.
RAISERROR with a state of 127
If RAISERROR is used within an osql script and a state of 127 is raised, osql will quit and return the message ID back to the client. For example:
RAISERROR(50001, 10, 127)
This error will cause the osql script to end and the message ID 50001 will be returned to the client.
The return values -1 to -99 are reserved by SQL Server; osql defines these values:
-100
Error encountered prior to selecting return value.
-101
No rows found when selecting return value.
-102
Conversion error occurred when selecting return value.
Can you post back if this works? I did not try it out...
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Edited by - tcartwright on 01/28/2003 3:19:04 PM
Tim C //Will code for food
January 28, 2003 at 3:48 pm
The key was to use raiserror with a state of 127. Thanks. Here is something that works
select zxcvxvsdfgsdfasegfwergbetbetgsefrwefwerwer
go
IF (@@ERROR <> 0)
RAISERROR ('command failed.', 16, 127)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply