CODEWITHSUNDEEP
sqlsql-servert-sqlsqlcmd
Ryan Gillooly
Ryan Gillooly

Reputation: 315

SQLCMD Connection error handling

When running multiple :CONNECT statements within a T-SQL script if one fails to connect, the rest of the script is not ran.

Is there some SQLCMD commands to work as a Try/Catch block to handle the connection error? I am aware that a T-SQL Try/Catch would not be valid as SQLCMD commands are ran before the SQL code.

Upvotes: 2

Views: 631

Answers (1)

Andrea
Andrea

Reputation: 12405

You can put your sqlcmd code inside a batch file and then execute the batch file (as suggested here).

You can use option -S to specify the server you want to connect to, and option -Q to specify your TSQL code (more info on MSDN).

For example:

sqlcmd -S WrongServerName -Q "select @@servername"
sqlcmd -S CorrectServerName -Q "select @@servername"

pause

the first command will fail (if WrongServerName is a non-existing or offline server), while the second one will be executed (if CorrectServerName is an online server) and will return the name of the server.

Upvotes: 1

Related Questions