How to capture errors from SQLCMD

04 January,2019 by Jack Vamvas

Question: I'm executing  code using SQLCMD from a batch file . The code points to a sql file and there is also an output file. 

SQLCMD -E -S MYSERVER\INST1  -i "setup_job_entry.sql" -o "setup_job_entry.log"

But I noticed that if the actual SQLCMD returns an error , for example , if I'm connecting to  an server which doesn't exist this error message will appear in the output file - but there will  not be an ERROR number , which would allow me to trap and return an appropriate message 

 

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

 

Is there a way to  fix this problem?

Answer:   You can add the  SQLCMD -b switch , which is one of the SQLCMD error options. This is a direct copy from SQL BOL 

-b on error batch abort
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).

If the sqlcmd script contains an incorrect comment, syntax error, or is missing a scripting variable, ERRORLEVEL returned is 1.

 

In your case you could add the -b switch and then add some conditional code in your batch file . For example: 

 

SQLCMD -E -S MYSERVER\INST1  -b -i "setup_job_entry.sql" -o "setup_job_entry.log"

IF %ERRORLEVEL% ==1 (
Echo SQLCMD RETURNED AN ERROR
) ELSE ( 
Echo SQLCMD DID NOT RETURN AN ERROR
) 




Read more on SQLCMD tips 

SQLCMD and blank password (SQL Server DBA)

SQL Server - SQLCMD with screen output and output file (SQL ...

Powershell : SQLCMD and invoke-expression (SQL Server DBA)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to capture errors from SQLCMD


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer