sqlcmd error handling

I did manage to work while in Barcelona last month, not just to visit. I delivered a session on sqlcmd, attended other sessions, and learned from others.

After the session, fellow PASS volunteer Thomas LaRock asked a great question regarding error handling. Pat Wright also talked about the same issue in this blog entry here. Pat, I tried to comment but couldn’t. Hopefully trackback will work at Sqljunkies’s blog site.

For example, take the code below (copy and paste the code into a .sql file, then run sqlcmd -i FileName.sql):

:On Error ignore

--Part 1
:connect ServerName1
select @@servername
go

--Part 2
:connect NonExistentServer
select @@servername
go

--Part 3
:connect ServerName2
select @@servername
go

The above code can be used to periodically check if Sql Server is online. The only trouble is, when it reaches Part 2 and connect to NonExistentServer, which does not exist or is down for whatever reason, the program will immediately terminate and Part 3 won’t be run. Why is that? Didn’t we specify to :On Error Ignore?

BOL (Book On Line) has this to say regarding :On Error:
Sets the action to be performed when an error occurs during script or batch execution.

:On Error only defines the behavior when you are running some scripts against a Sql Server instance, when you are connected to a server. In other words, it only governs the error conditions of t-sql statement, not commands for sqlcmd. It fails in the above example because the failure point is at the connection time, not when it is executing sql code.

Let’s look at another example:

:On Error ignore
:connect ServerName1

--Part 1
select @@VVservername
go

--Part 2
select @@servername
go

In the code above, Part 1 will error out, since @VVservername is invalid. However, Part 2 will run because that t-sql error will be ignored.

To solve the issue listed in the first example, consider put the following code in a batch file and run the batch file instead:

sqlcmd -S ServerName1 -Q "select @@servername"
sqlcmd -S NonExistentServer -Q "select @@servername"
sqlcmd -S ServerName2 -Q "select @@servername"

Leave a Reply