Forcing a Job Step to fail from within a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Forcing a Job Step to fail from within a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-11 : 07:40:00

John writes "I have the following job step :

osql -E -Q"exec Check_Call_Counts"

which runs the following stored procedure :

CREATE PROCEDURE PEDATSADMIN.Check_Call_Counts AS

DECLARE @v_TCDCount INT,
@v_ACDCount INT

DECLARE ATS_Cursor CURSOR FOR
SELECT COUNT(*)
FROM ATS_Detail tcd
WHERE tcd.DateTime BETWEEN DATEADD(DAY,-1,GETDATE()) AND GETDATE()
AND LEN(tcd.ANI) = 10

DECLARE Term_Cursor CURSOR FOR
SELECT COUNT(*)
FROM [001-XYZ-S].ats_wadb.dbo.Call_Detail tcd
WHERE tcd.DateTime BETWEEN DATEADD(DAY,-1,GETDATE()) AND GETDATE()
AND LEN(tcd.ANI) = 10

OPEN ATS_Cursor
FETCH NEXT FROM ATS_Cursor INTO @v_ACDCount
CLOSE ATS_Cursor
DEALLOCATE ATS_Cursor

OPEN Term_Cursor
FETCH NEXT FROM Term_Cursor INTO @v_TCDCount
CLOSE Term_Cursor
DEALLOCATE Term_Cursor

IF @v_TCDCount != @v_ACDCount
BEGIN
???
END
GO

I need to put something in when the counts are different to make the job step fail. Any ideas ?

Thanks"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-11 : 07:42:55

1. Why are you using osql in a job step? Just run the stored procedure as a T-SQL command step, it's a lot easier.
2. Why are you using cursors?

Try this:

CREATE PROCEDURE PEDATSADMIN.Check_Call_Counts AS
IF (SELECT COUNT(*) FROM ATS_Detail tcd
WHERE tcd.DateTime BETWEEN DATEADD(DAY,-1,GETDATE()) AND GETDATE() AND LEN(tcd.ANI) = 10) <>
(SELECT COUNT(*) FROM [001-XYZ-S].ats_wadb.dbo.Call_Detail tcd
WHERE tcd.DateTime BETWEEN DATEADD(DAY,-1,GETDATE()) AND GETDATE() AND LEN(tcd.ANI) = 10)

RAISERROR('The counts do not match.', 16, 1)

The RAISERROR statement will cause the job step to report failure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources