Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

error handling Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:39 AM
Points: 6, Visits: 27
Hello,
Can some one help me with a way that I can stop scripts from running if one of the scripts fail.

I have one sql script that will run a number of sql scripts using the r command
how ever I need to know if there is another way to stop the rest of the scripts from running if one fails.
I currently have something like this:

I created on script sbuildn.sql. this script is as follows:

SET NOCOUNT ON
GO

:On Error exit

PRINT ' '
PRINT 'Starting: Initialization script'
:r "P:\IMP\ini1.sql"
GO

PRINT ' '
PRINT 'Starting: Platform build'
:r "P:\IMP\ini2.sql"
GO

from the command prompt, I run:
SQLCMD -S Devlair-db -i "sbuildn.sql"

Is there another way to run this script so that an error in one will stop the execution of any further script?
Any help will be highly appreciated
Ugo
Post #1356801
Posted Monday, September 10, 2012 11:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 340, Visits: 808
The issue is, you are specifying the GO batch separator so if you encounter an error in one batch, the subsequent batches will still execute.

If you encounter an error you can try raising an error with severity 20. This will end the current connection completely, preventing subsequent batches from running. You must specify the WITH LOG option at the end...

RAISERROR('Script Failed!', 20, 1) WITH LOG

See how that works for you.

John
Post #1356930
Posted Thursday, September 13, 2012 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:39 AM
Points: 6, Visits: 27
Thank you,
I added the
RAISERROR('Script Failed!', 20, 1) WITH LOG
and it closed the connection preventing subsequent scripts from running!
Post #1358492
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse