THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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?

 

Published Friday, August 29, 2008 5:28 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement