error handling

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » error handling

error handling

Rate Topic

Display Mode

Topic Options

Author

Message

ugo boy

Posted Monday, September 10, 2012 8:59 AM

Forum 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

JohnnyDBA

Posted Monday, September 10, 2012 11:55 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:36 AM
Points: 334, Visits: 706

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

ugo boy

Posted Thursday, September 13, 2012 5:55 AM

Forum 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 »

Permissions