Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Running Commands against two Servers in a single query window

Answered Running Commands against two Servers in a single query window

  • Saturday, February 02, 2013 7:34 AM
     
     
    Sign In to Vote
    0

    I have just been watching a training video where the presenter had connections to two servers in SSMS Object Explorer, lets call then ServerOne and ServerTwo.

    He had ONE query window open and seemed to be running commands against both connections. Something like:

    :CONNECT ServerOne

    SELECT * FROM sys.dm_xe_objects;

     

    :CONNECT ServerTwo

    SELECT * FROM sys.dm_xe_objects;

    The presentation was to show the differences between Extended Events in SQL2008R2 and SQL2012.

    He seemed to refer to it as a 'SQL command window' although I am not sure if that was just another name for what I would call a query window.

    I tried to do a web search on this and just get references to the old SQL command line interface.  Could any one provide further information on what is happening here?

     

     

     


     

All Replies

  • Saturday, February 02, 2013 8:00 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Here's how.

    Go to the Registered Servers window.

    Right click on Local Server Groups, click New Query.

    Enter a query like one below and execute it.

    It will run on all the servers.

    The last server is Windows Azure SQL Database instance.

    SELECT ObjectCount = COUNT(*) FROM sys.objects;
/*
Server Name	ObjectCount
HPESTAR\SQL12	89
HPESTAR	         90
azurexx8r.database.windows.net	42
*/

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback/Kindle: SQL Server 2012 Pro

     
  • Saturday, February 02, 2013 4:24 PM
     
     Answered Has Code
    Sign In to Vote
    0

    The trainer was using Management Studio in SQLCMD mode.  From the main menu:

    When you're in SQLCMD Mode, you have access to a number of special commands, including:

    :connect                     allows you to connect to another server from the script

    :r                              run the given .sql file

    :setvar                      set a variable which has global scope

    :on error exit             script will stop after an error

    :exit                         script wil stop at that point

    !!                             run any dos command, eg dir, del, call etc

    SQLCMD Mode is really powerful!  Here's a simple example:

    :on error exit

:setvar myServer1 .\sql2005
:setvar myServer2 .\sql2012

:connect $(myServer1)
:r c:\temp\temp.sql
GO

:connect $(myServer2)
:r c:\temp\temp.sql
    Read this article for more details
    http://msdn.microsoft.com/en-gb/library/ms174187.aspx
     
  • Saturday, February 02, 2013 4:43 PM
     
     
    Sign In to Vote
    0

    That's it!!

    Thanks, never noticed that option before.

    Thanks.