November 19, 2004 at 12:00 pm
Hi,
I have been asked to set up a job that should inform a specific operator when certain columns in a given table go NULL. The job needs to run every couple hours daily.
I have been able to setup everything else except the actual 'job step'. That is where I am confused. I understand the job step has a '@subsystem' option and a '@command' option. I have specified '@subsystem' as 'TSQL' and '@command' as 'exec stored-proc'.
Now I need to write the stored procedure such that the error raised within the procedure (must be raised when one of those columns go NULL) must propagate up to the job step, fail that job step, and hence fail the job too. I am just not sure how to do that?. My stored procedure is pasted below.
---------------------------------------------------------------
CREATE PROCEDURE usp_displayusers
@custom1 int
AS
SET @custom1 = (SELECT count(*) FROM EMPLOYEE
WHERE custom1 = NULL)
IF @custom1 > 0
BEGIN
RETURN 1
END
ELSE IF (@custom1 = 0)
BEGIN
RETURN 0
END
GO
---------------------------------------------------
I am not even sure if I am raising an error the right way.
Put in other words, my main problem is: How to cause a stored procedure, hence a job step, and hence a job to fail when one of the columns in a table goes null?
Please advise.
Thanks,
Sharad
November 19, 2004 at 1:22 pm
that way your usp_ only returns a value.
use raiserror of you want to have an error raised and set the jobstep's "on error" action to "quit job with failure"
Check BOL.
e.g. raiserror ('EMPLOYEE custom1 null', 16,1)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 19, 2004 at 1:51 pm
Thanks I will try that!.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply