Exiting sqlcmd with RAISERROR(50001,10,127)

Question

Post reply

Exiting sqlcmd with RAISERROR(50001,10,127)

Michael Lato

Ten Centuries

Points: 1278
More actions
August 2, 2012 at 1:28 pm

#279813

I am trying to set up some upgrade scripts using sqlcmd but I need a graceful exit from the script under certain conditions (i.e. wrong database version). According to Books Online, I can use RAISERROR(50001,10,127) to force sqlcmd to stop running a script immediately and to return an errorlevel of 50001. However, this is not working.
I am running SQL Server 2012 Developer edition on a 64-bit machine and I'm using the two scripts below. Has anyone else seen this behaviour?
sql_test.sql
RAISERROR(50001, 11, 127)
GO
PRINT 'This should not print but does!'
GO
sql_test.cmd
cls
set myS=.\SQLEXPRESS
set myD=dba
set myU=-E
if exist sql_test.txt del sql_test.txt
sqlcmd -S %myS% %myU% -d %myD% -l 60 -I -i sql_test.sql >> sql_test.txt 2>&1
echo %errorlevel%
Regards,
Michael Lato

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You have a GO which is a batch terminator.

Try this:

begin try

RAISERROR(50001, 11, 127)

PRINT 'This should not print but does!'

end try

begin catch

print 'The above print line did not print because the error was encountered'

end catch

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Michael Lato Ten Centuries Points: 1278 More actions · Answer 2

Hello Sean,

I need the GO terminator - these scripts create procedures, drop tables, etc. Does the terminator override the cancellation?

Regards,
Michael Lato

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think raising an error of level 20 disconnects the connection, so no further processing after the error would occur regardless of terminators; would that help instead?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Michael Lato (8/2/2012)
I am trying to set up some upgrade scripts using sqlcmd but I need a graceful exit from the script under certain conditions (i.e. wrong database version). According to Books Online, I can use RAISERROR(50001,10,127) to force sqlcmd to stop running a script immediately and to return an errorlevel of 50001. However, this is not working.
I am running SQL Server 2012 Developer edition on a 64-bit machine and I'm using the two scripts below. Has anyone else seen this behaviour?

The RAISERROR works as advertised for me when using sqlcmd v10.5. I do not have a SQL 2012 instance close, but will test when I can.

I just learned about the RAISERROR option you mentioned on this post, so thanks for that. I too have used sqlcmd as a means to deploy SQL scripts containing many batches separated by GO and have used the -b switch to achieve this type of behavior. Maybe the -b switch will work for you too.

sqlcmd Utility

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

I was able to recreate the issue you initially reported with sqlcmd v11.0. The good news is that the -b option works as advertised and stops the batch when the initial error is raised.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Michael Lato Ten Centuries Points: 1278 More actions · Answer 6

Thank you for the confirmation - this was really driving me crazy. I've opened a Connect item for it. If you have a chance, please upvote it!

https://connect.microsoft.com/SQLServer/feedback/details/756984/sqlcmd-doesnt-exit-when-using-raiserror-50001-10-127

Regards,
Michael Lato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Cool. You're welcome. I upvoted the item.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato