How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

Question

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.

Answer 1

You should use the option -b in sqlcmd.

-b Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0

http://msdn.microsoft.com/en-us/library/ms162773.aspx

Answer 2

From the MSDN SQLCMD Utility page at: http://msdn.microsoft.com/en-us/library/ms162773.aspx

If RAISERROR is used within a sqlcmd script and a state of 127 is raised, sqlcmd will quit and return the message ID back to the client. For example:

RAISERROR(50001, 10, 127)

So you could wrap the BACKUP DATABASE... command in a TRY...CATCH block and raise the appropriate error message, which sqlcmd would then return to your batch file.

For instance, the consider the following errorleveltest.sql file:

:ON ERROR EXIT
BEGIN TRY
    /* creates error 3147 Backup and restore operations 
            are not allowed on database tempdb */
    BACKUP DATABASE tempdb; 
END TRY
BEGIN CATCH
    DECLARE @msg NVARCHAR(255);
    SET @msg = 'An error occurred: ' + ERROR_MESSAGE();
    RAISERROR (50002, 10, 127);
END CATCH

And the following .bat file: (first line is wrapped for readability, but needs to be on a single line.)

@echo off
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S "someinstance" -E 
    -i .\errorleveltest.sql
ECHO Errorlevel: %ERRORLEVEL%

This returns the following from my cmd.exe prompt:

Msg 18054, Level 16, State 1, Server CP708-D377\MV, Line 9
Error 50002, severity 10, state 127 was raised, but no message with that error number 
was found in sys.messages. If error is larger than 50000, make sure the user-defined 
message is added using sp_addmessage.
Errorlevel: 1

As you can see, ERRORLEVEL 1 is returned instead of the normal return value of 0. I'm unable to get the ERRORLEVEL to reflect the actual error number, in this case 50002; perhaps there is an issue in my code, or perhaps there is some problem with my environment.

Answer 3

I solved this with this command

sqlcmd -V1 -m0 -r1 -S dbHost -U dbUser -P dbPass -Q "SELECT 1 FROM xxxxxxxxx" 1>nul