September 10, 2012 at 8:59 am
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
September 10, 2012 at 11:55 am
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
September 13, 2012 at 5:55 am
Thank you,
I added the
RAISERROR('Script Failed!', 20, 1) WITH LOG
and it closed the connection preventing subsequent scripts from running!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply