July 16, 2001 at 9:21 am
Hi all
Ive been using the "execute sql task" to run my stored procedures, only to find that if I use the RAISEERROR this does not propogate out as a "failed" task so the DTS just continues on...
ie.
A1 -----success----A2---->etc---->
CREATE PROCEDURE A1 AS
RAISERROR ('An error occured...',10,5) WITH SETERROR
GO
so running this DTS, A1 will continue onto A2 no matter what RAISEERROR i place in.
Funny enough, a SQL*Server generated error WILL result in a error and stop the DTS.
Any ideas? im not keen on replacing my tasks with Active-X jobs and checking return values it seems to totally defeat the purpose of the SQL task!?
Cheers
Chris.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 16, 2001 at 10:05 am
Well Chris you #%%@!$ 🙂
Played with it a little.. all comes down to the use of the severity level, anything over 10 will raise the error correctly in the DTS and stop the next job running (assuming you are using a success workflow). Of course, read up re severity levels as there are numerous restrictions over 16.
RAISERROR ('An error occured..',11,1)
..so the above works..
Cheers
Chris.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply