2

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for this. Is there any solutions other than using batch files? The reason I ask is that the scripts are taken from a column inside a table and then joined together to make one large script that runs through multiple servers
@RyanGillooly Yes, I see your point: I don't know if there is a more advanced mechanism for error handling in SQLCMD. I use this approach because I execute simple TSQL instructions, so batch files are a good solution for me. I understand that if you have to run complex scripts this solution would probably be inadequate. I once read about ERRORLEVEL variable, but I think it only handles errors returned by DOS commands. I'm sorry that I am unable to help you further.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.