How do I pass an exit code from sqlcmd?

How do I pass an exit code from sqlcmd to the batch file that is calling it? It turned out this is not a simple issue. The research will take you into understanding the sqlcmd.exe. Understanding transact-sql. Knowing how the options provided by sqlcmd.exe are confusing and unspecific. See how one TINY opening is left with raiserror function and you have to pretty much go for the precise bulls eye and a very specific parameters supplied to the raiserror to make this work.

How do I pass an exit code from sqlcmd?

Search for: How do I pass an exit code from sqlcmd?

Here are some answers from SOF

What is SQLCMD ON ERROR EXIT instruction

Search for: What is SQLCMD ON ERROR EXIT instruction

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. If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd does not report errors for severity level 10 (informational messages).

How to exit sqlcmd if backup command fails in sqlserver?

Search for: How to exit sqlcmd if backup command fails in sqlserver?

Exiting with errorlevel when backup command fails in sqlcmd in sqlserver?

Search for: Exiting with errorlevel when backup command fails in sqlcmd in sqlserver?

RETURN command in sqlcmd

Search for: RETURN command in sqlcmd

raiserror in sqlcmd.exe

Search for: raiserror in sqlcmd.exe

sqlcmd.exe reference

Search for: sqlcmd.exe reference

This is the closest reference on sqlcmd I got


: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


BEGIN TRY
    BACKUP DATABASE tempdb; 
END TRY
BEGIN CATCH
 :Exit(1)
END CATCH


:ON ERROR EXIT
BEGIN TRY
    BACKUP DATABASE tempdb; 
END TRY
BEGIN CATCH
    RAISERROR (50002, 10, 127);
END CATCH


GO [count]  :List
[:] RESET   :Error
[:] ED      :Out
[:] !!      :Perftrace
[:] QUIT    :Connect
[:] EXIT    :On Error
:r          :Help
:ServerList   
:XML [ON | OFF]
:Setvar   
:Listvar

All sqlcmd commands, except GO, must be prefixed by a colon (:).

sqlcmd commands are recognized only if they appear at the start of a line.

All sqlcmd commands are case insensitive.

Each command must be on a separate line. A command cannot be followed by a Transact-SQL statement or another command.

Commands are executed immediately. They are not put in the execution buffer as Transact-SQL statements are.

transact-sql reference raiseerror

Search for: transact-sql reference raiseerror

Here is raiseerror reference

This is a better reference for raiserror


DECLARE @DBID INT;
SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();

RAISERROR
    (N'The current database ID is:%d, the database name is: %s.',
    10, -- Severity.
    1, -- State.
    @DBID, -- First substitution argument.
    @DBNAME); -- Second substitution argument.
GO


raiserror (
   50001, -- user generared error ids need to be > 50000
   10, -- severity
   1 -- state, where it happened - context )

raiserror severity and state values

Search for: raiserror severity and state values

upto Severity level 10 are informational messages.

11-16 are considered errors that can be fixed by the user.

17-19 are considered Non-Fatal errors in Sql Server Resources, Engine and other stuff .

20-25 are considered Fatal Error which causes sql server to shut down the process immediately.

This link on SOF is more specific

When your intention is to log a warning but continue execution, use a severity level below 10 instead

seem to be like 10, but if 10 is a warning, 16 is error but continue execution.

what is the meaning of raiserror state 127

Search for: what is the meaning of raiserror state 127

raiserror severity and catch

Search for: raiserror severity and catch

Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY?CATCH block. However, TRY?CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed.

Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY?CATCH blocks.

This link has more explanation of the behavior of raiserror

RAISERROR that has a severity of 11 to 19 executed inside a TRY block causes control to transfer to the associated CATCH block. RAISERROR that has a severity of 11 to 19 executed inside a CATCH block returns an error to the calling application or batch. In this way, RAISERROR can be used to return information to the caller about the error that caused the CATCH block to execute. Error information provided by the TRY?CATCH error functions can be captured in the RAISERROR message, including the original error number; however, the error number for RAISERROR must be >= 50000.

RAISERROR that has a severity 10 or lower returns an informational message to the calling batch or application without invoking a CATCH block.

This is surprising. Because a raiserror returns exiting the block...

RAISERROR that has a severity 20 or higher closes the database connection without invoking the CATCH block.

raiserror state of 127 try catch

Search for: raiserror state of 127 try catch

special status of 127 in raiserror

Search for: special status of 127 in raiserror

Error Handling in SQL 2000 ? a Background - Online Book Ref

As I mentioned State is rarely of interest. With RAISERROR, you can use it as you wish. If you raise the same message in several places, you can provide different values to State so that you can conclude which RAISERROR statement that fired. The command-line tools OSQL and ISQL have a special handling of state: if you use a state of 127, the two tools abort and set the DOS variable ERRORLEVEL to the message number. This can be handy in installation scripts if you want to abort the script if you detect some serious condition. (For instance, that database is not on the level that the installation script is written for.) This behaviour is entirely client-dependent; for instance, Query Analyzer does not react on state 127.


raiserrror(
  500001, --user defined
  10, -- just return with this severity
      -- No need to invoke a catch block
      -- No issue anyway if in a catch block already
      -- Just in case of a nested stuff, get out
  127 -- just abort back to the command line with errorlevel
)

The -b option seem to work on any error. it is not specific. So I don't want to use it as I am not sure what is considered an error and what is not. So I have not tested that option well.

I haven't used the :commands like :exit as their behavior is weird based on where they are placed in the .sql file. For example they have to be on the beginning of the line and not followed by any etc. For example the :exit never worked in a catch for me.

That left me to focus on raiserror. Who knew this is so involved as to the precise nature of the 3 parameters. Hope these notes explain the unnecessary mystery around these parameters and raiserror and how the sqlcmd.exe treats the state code 127 so special and unique!!

I hate specializations!!!