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

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

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

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

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS, T-SQL Tagged: language sql, microsoft sql server, SSMS