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.
satya - 8/14/2016, 1:03:56 PM
How do I pass an exit code from sqlcmd?
How do I pass an exit code from sqlcmd?
satya - 8/14/2016, 1:08:02 PM
What is SQLCMD ON ERROR EXIT instruction
What is SQLCMD ON ERROR EXIT instruction
satya - 8/14/2016, 1:11:08 PM
The -b option
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).
satya - 8/14/2016, 1:11:59 PM
How to exit sqlcmd if backup command fails in sqlserver?
How to exit sqlcmd if backup command fails in sqlserver?
Search for: How to exit sqlcmd if backup command fails in sqlserver?
satya - 8/14/2016, 1:13:41 PM
Exiting with errorlevel when backup command fails in sqlcmd 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?
satya - 8/14/2016, 1:16:32 PM
RETURN command in sqlcmd
RETURN command in sqlcmd
satya - 8/14/2016, 1:23:54 PM
raiserror in sqlcmd.exe
raiserror in sqlcmd.exe
satya - 8/14/2016, 1:25:40 PM
sqlcmd.exe reference
sqlcmd.exe reference
satya - 8/14/2016, 1:26:36 PM
This is the closest reference on sqlcmd I got
satya - 8/14/2016, 1:30:16 PM
Understand this code
: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
satya - 8/14/2016, 1:31:01 PM
why do all that?? why not this?
BEGIN TRY
BACKUP DATABASE tempdb;
END TRY
BEGIN CATCH
:Exit(1)
END CATCH
satya - 8/14/2016, 1:53:27 PM
For some reason the later doesn't work, only teh following does
:ON ERROR EXIT
BEGIN TRY
BACKUP DATABASE tempdb;
END TRY
BEGIN CATCH
RAISERROR (50002, 10, 127);
END CATCH
satya - 8/15/2016, 11:16:38 AM
sqlcmd commands
GO [count] :List
[:] RESET :Error
[:] ED :Out
[:] !! :Perftrace
[:] QUIT :Connect
[:] EXIT :On Error
:r :Help
:ServerList
:XML [ON | OFF]
:Setvar
:Listvar
satya - 8/15/2016, 11:18:00 AM
Rules how they can show up
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.
satya - 8/15/2016, 11:22:38 AM
transact-sql reference raiseerror
transact-sql reference raiseerror
satya - 8/15/2016, 11:32:50 AM
This is a better reference for raiserror
satya - 8/15/2016, 11:33:06 AM
An example to illustrate
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
satya - 8/15/2016, 11:37:15 AM
Or
raiserror (
50001, -- user generared error ids need to be > 50000
10, -- severity
1 -- state, where it happened - context )
satya - 8/15/2016, 11:37:32 AM
raiserror severity and state values
raiserror severity and state values
satya - 8/15/2016, 11:39:40 AM
Out of the box severity level definition
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.
satya - 8/15/2016, 11:42:24 AM
Level 10
When your intention is to log a warning but continue execution, use a severity level below 10 instead
satya - 8/15/2016, 11:45:05 AM
Level 16
seem to be like 10, but if 10 is a warning, 16 is error but continue execution.
satya - 8/15/2016, 11:45:29 AM
what is the meaning of raiserror state 127
what is the meaning of raiserror state 127
satya - 8/15/2016, 11:53:30 AM
raiserror severity and catch
raiserror severity and catch
satya - 8/15/2016, 11:55:50 AM
Few notes
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.
satya - 8/15/2016, 11:57:01 AM
This link has more explanation of the behavior of raiserror
satya - 8/15/2016, 11:59:45 AM
Severity of 11 to 19 will transfer control to catch
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.
satya - 8/15/2016, 12:01:12 PM
Severity of 10
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...
satya - 8/15/2016, 12:01:47 PM
Severity 20 or higher
RAISERROR that has a severity 20 or higher closes the database connection without invoking the CATCH block.
satya - 8/15/2016, 12:04:28 PM
raiserror state of 127 try catch
raiserror state of 127 try catch
satya - 8/15/2016, 12:05:29 PM
special status of 127 in raiserror
special status of 127 in raiserror
satya - 8/15/2016, 12:08:38 PM
Error Handling in SQL 2000 ? a Background - Online Book Ref
satya - 8/15/2016, 12:09:56 PM
This book states....
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.
satya - 8/15/2016, 12:12:29 PM
This long winding obsession results in the right raiserror
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
)
satya - 8/15/2016, 12:17:49 PM
Why do I choose not to use -b or the :exit like commands
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!!!