:CONNECT in SSMS

7

March 13, 2014 by Kenneth Fisher

Those people who are familiar with SQLCMD will recognize this command. It is used to connect to an instance from within a SQLCMD script. What they may not realize is that this command (and other SQLCMD commands) can be used in a query window by turning on SQLCMD mode. There is a great overview of using SQLCMD commands in SSMS here.

I am going to highlight an interesting aspect of the CONNECT command in SSMS.

First a basic example. Note: all of the connections start on the instance (local)\SQL2012

SELECT @@SERVERNAME
GO
:CONNECT (local)\SQL2008R2
SELECT @@SERVERNAME
GO
:CONNECT (local)\SQL2012
SELECT @@SERVERNAME
GO

With an output of

CONNECT1

So using :CONNECT you can change connections inside a script. I’ll frequently use this technique to get a piece of information from a number of different servers at once.

For example, how many databases do I have on each server?

:CONNECT (local)\SQL2012
SELECT @@SERVERNAME, COUNT(1) FROM sys.databases
GO
:CONNECT (local)\SQL2008R2
SELECT @@SERVERNAME, COUNT(1) FROM sys.databases
GO

CONNECT2

There are a couple of odd aspects to the CONNECT command. The connect command happens at the beginning of the batch regardless of where in the batch it is, and if you have more than one of them in a single batch only the last one counts. I did have a couple of runs where I got different results but I couldn’t reproduce them and they only happened once or twice so it may have been a PEBCAK issue. Again remember that my connections are all initially on (local)\SQL2012.

SELECT @@SERVERNAME
:CONNECT (local)\SQL2008R2

CONNECT4

SELECT @@SERVERNAME
:CONNECT (local)\SQL2008R2
SELECT @@SERVERNAME
:CONNECT (local)\SQL2012
SELECT @@SERVERNAME
GO
:CONNECT (local)\SQL2012
SELECT @@SERVERNAME
:CONNECT (local)\SQL2008R2
SELECT @@SERVERNAME
GO

CONNECT3

7 thoughts on “:CONNECT in SSMS

  1. SQLAndy says:

    I’ve seen it behave wierdly a couple times too, but never quite figured out why. It made me think that the best approach is to make sure the query is initially disconnected. I tend to hold connections open and if the connect goes strange, it could run on the wrong instance. Or maybe I was just tired!

    • The weird behavior does worry me sometimes but I’ve never had a problem as long as I only have one :CONNECT in a batch. The first time I tested this out and got unusual results I honestly thought I was just seeing things. Tried it again and then I knew I was. Until it happened again a while later.

  2. Great post– I’m a huge fan of SQLCMD in SSMS to simplify some tasks. It can be tricksy though: I love your example with the batches and agree that it’s worth adding in validation/error handling to make sure you’re really connected to the place you think you are!

    • Thanks! I appreciate the comment. :CONNECT in general has given me some weird results. It tends to make me more cautious. I still use it for a quickie check of multiple instances though.

      • Mike says:

        I’ve learned the most about :CONNECT when I used the ‘New Availability Group Wizard’ to configure my AG, then generated the script right before executing. Loads of :CONNECT statements there, and it *seems* like the GO statement breaks up the connections between the various instances in the AG. Like you, Kendra and Kenneth, I’ve seen strangeness when I try to hand-code multiple :CONNECT statements and am a bit leery about running them.

  3. Yeti says:

    Why you use COUNT(1) instead of COUNT(*) ? Does it have any performance-wise justification?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,657 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: