SqlServer : T-Sql Connect to Other Servers

by bitznbitez

How do you connect to different servers in a TSql script ?   Sure there are linked servers but sometimes you just want to be connected to a different server.   To keep it simple lets assume you are using windows athentication and all your servers are on the same domain.

The solution is to first enable SQLCMD.  In SSMS Query->SQLCMD Mode will toggle this on and off.   Then in your sql you can use the :connect command.

Lets assume you are already connected to a server named SqlServer1 select @@SERVERNAME

select @@SERVERNAME
go
:connect SqlServer2
select @@SERVERNAME
go
:connect SqlServer3
select @@SERVERNAME
go

You will notice we are using “go” at the end of each block.  This is important to ensure your commands run against the correct server.  The above script will display

  • SqlServer1
  • SqlServer2
  • SqlServer3

This is more of a trivia item for me than anything else at the moment.

Advertisements