04 January,2019 by Tom Collins
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)
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |