October 3, 2002 at 11:19 am
I created a sql job with sql job step as t-sql. Within t-sql statements I have If conditions to check some status. How can I make that jobstep failed or execute another job step within same job based on my if condition results.
Thanks
October 3, 2002 at 11:38 am
Possibly raiserror would cause it to fail, have not tried it. You could write a flag to a table/file, then have the next step check it to see whether or not to run.
Andy
October 3, 2002 at 11:39 am
use the classic divide by zero trick, like so when you want to bomb the code being executed by a TSQL script.
select 1/0
This will cause the step to fail.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 3, 2002 at 1:28 pm
If it doesn't have to fail, but just needs to exit the current Step then use a GOTO statement
-------------------------
IF <condition> GOTO goodbye
--else perform the other operations.
:goodbye
--------------------
If you absolutely need the job to fail then the divide by zero error sounds like a plan
🙂
"Keep Your Stick On the Ice" ..Red Green
October 3, 2002 at 1:33 pm
Thanks for your help. Select 1/0 followed by return statement did work.
October 3, 2002 at 6:53 pm
It is considered safer and better to use the RAISERROR method than divide by zero. It is also considered poor programming to use divide by zero on purpose (standard practice, I believe as can be the root of many memory issues if leaks occurr). RAISERROR will also allow you to specifically give an output which will store in the jobs history thus allowing you the ability to know where the failure was forced if you need to do multiple times.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply