August 10, 2012 at 9:34 am
good morning guys, i have a tsql script being run from a job, i want the job to fail if the value from the tsql script returns false, how do i this; example;here is my script;
and my job name is called "executeTaskfileNotify"
select distinct fileexists
from fileNotify
where fileexists = 'false'
i want the job to fail if this script returns the value "false"
August 10, 2012 at 9:48 am
The easiest way i can think of is to use the RAISERROR - Try something like this:
DECLARE @Result bit
SET @Result = (
SELECT DISTINCT
fileexists
FROM fileNotify
WHERE fileexists = 'false'
)
IF (@Result = 0)
BEGIN
RAISERROR ('Force SQL Agent Job Failure!', 16, 1)
END
END THis should force the result to the Agent job
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 10, 2012 at 10:57 am
Thank you, i will try that and let you know if it works. thanks
August 10, 2012 at 10:58 am
No sweat, let me know and good luck!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 10, 2012 at 11:52 am
i tried it and it didnt work, any more ideas
August 10, 2012 at 12:14 pm
How many steps are in your job?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 10, 2012 at 12:26 pm
I think the problem was that fileNotify.fileexists is not a bit type. This should work:
DECLARE @Result INT
SET @Result = (
SELECT COUNT(*)
FROM fileNotify
WHERE fileexists = 'false'
)
IF (@Result <> 0)
BEGIN
RAISERROR ('Force SQL Agent Job Failure!', 16, 1)
END
END
...
August 10, 2012 at 12:32 pm
worked like a charm,it was just some syntax errors, can you explain what is the severity message and no , thanks so much
August 10, 2012 at 12:42 pm
For the RAISERROR statement, the text can be anything you want. The severity is the highest severity number in the "can be corrected by the user" range (see http://msdn.microsoft.com/en-us/library/aa937483(v=sql.80).aspx for more details on severity).
It doesn't really matter what severity you use, you just want some error to be thrown so the job fails. We just get used to using severity 16 errors because anything higher is reserved for resource, internal, or critical errors.
Here is the link for the RAISERROR statement: http://msdn.microsoft.com/en-us/library/ms178592.aspx.
...
August 10, 2012 at 12:46 pm
I think the problem was that fileNotify.fileexists is not a bit type. This should work:
Whoooooops! :w00t: When I test it with a temp table I used a bit and forgot to change it when I posted the code! FAIL
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply