SQLCMD mode and batch separators

I fell for this one this week. If you execute the following code in SQLCMD mode, what would you expect the output to be?

:SETVAR PrincipalServer WINSTD2K8BASE

:SETVAR MirrorServer WINSTD2K8BASE\SQLDEV02

:SETVAR WitnessServer WINSTD2K8BASE\SQLDEV03

:CONNECT $(PrincipalServer)

SELECT @@SERVERNAME;

:CONNECT $(MirrorServer)

SELECT @@SERVERNAME;

:CONNECT $(WitnessServer)

SELECT @@SERVERNAME;

I’m guessing you might not have expected:

WINSTD2K8BASE\SQLDEV03

WINSTD2K8BASE\SQLDEV03

WINSTD2K8BASE\SQLDEV03

The problem is the lack of a batch separator. What I should have written was this:

:SETVAR PrincipalServer WINSTD2K8BASE

:SETVAR MirrorServer WINSTD2K8BASE\SQLDEV02

:SETVAR WitnessServer WINSTD2K8BASE\SQLDEV03

:CONNECT $(PrincipalServer)

SELECT @@SERVERNAME;

GO

:CONNECT $(MirrorServer)

SELECT @@SERVERNAME;

GO

:CONNECT $(WitnessServer)

SELECT @@SERVERNAME;

GO

While this may be strictly correct, I can’t imagine it’s the behaviour anyone would wish for. Do you think that a :CONNECT statement in a SQLCMD batch should also be treated as a batch separator? Does it ever make sense for it not to?

2008-08-29