51

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.

1
  • 3
    For what it's worth, at my current job, we use Ola Hallengren's backup stored procedures (with some in-house wrapper stored procedures around them) to backup thousands of databases across hundreds of servers, and we've had no problems with them. Sep 22, 2014 at 18:10

3 Answers 3

72

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

12

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.

3
  • 2
    I've found that this ONLY works if you supply a numeric value as the first parameter of RAISERROR. for example: This returns a %errorlevel% = 1: RAISERROR(50002, 10,127) But this returns %errorlevel% = 0: RAISERROR('myErrMsg.', 10,127)
    – user73342
    Aug 21, 2015 at 20:43
  • 7
    FYI regarding the last paragraph: ERRORLEVEL shouldn't be expected to be the same value as the Error Number reported from SQL Server. The ErrorNumber is a SQL Server-specific value to indicate why it (i.e. SQL Server) terminated. On the other hand, ERRORLEVEL is a SQLCMD-specific value to indicate why it (i.e. SQLCMD) terminated. Dec 4, 2015 at 19:00
  • @SolonmonRutzky Good point. I submitted a PR for improving the sqlcmd Utility Best Practices section this morning, and incorporated your note here (with edits to make the phrasing consistent with other parts of the page, e.g. "DOS ERRORLEVEL" rather than "ERRORLEVEL"). github.com/MicrosoftDocs/sql-docs/pull/5858 Jan 1, 2021 at 17:43
-1

I solved this with this command

sqlcmd -V1 -m0 -r1 -S dbHost -U dbUser -P dbPass -Q "SELECT 1 FROM xxxxxxxxx" 1>nul
2
  • 4
    You may want to add a little explanation of what the command line arguments mean, this will make the answer more useful.
    – mustaccio
    Feb 12, 2021 at 19:57
  • This might be useful for something, but it isn't an answer to the question.
    – daveloyall
    Jun 15, 2023 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.