CODEWITHSUNDEEP
sql-servercommand-line
Wes Palmer
Wes Palmer

Reputation: 880

Query multiple SQL Servers in One query

I am trying to set up a query that will grab the Windows version of each SQL Server I have and throw it into a table. I have the query that grabs the version but I think there is a better way to get the information needed than connecting to each indiviual server one by one to run the query. I am not opposed to using XP_cmdshell I am just wondering if there is a way to run one query that will grab the version of each Windows OS I have on the sql servers. Also I do have a list of servers to use.

EDIT: I know I wil have to in some way touch each server. I would just like a way to get around having the RDP to each server and open SQL server and query it or haveing to connect to each server within sql server and running the query one by one.

All I have right now code wise is a simple INSERT STATEMENT I get here and I draw a blank on where to go next of even hoe to tackle the problem. The table below has two columns ServerName and Win_Ver ServerName is already populated with all the servers I have.

INSERT INTO mtTable
(Win_Ver)

SELECT @@Version

Upvotes: 1

Views: 818

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48806

Given that:

  • there are "roughly 112 servers"
  • the servers being a "mixture between 2008 - 2012"
  • "There is table we are keeping with all of our DB server Statistics."
  • and "We periodically get asked to produce these statistics"

one option is to cycle through that table of servers using a cursor, and for each one, execute xp_cmdshell to call SQLCMD to run the query. You would use a table variable to capture the result set from SQLCMD as returned by xp_cmdshell. Something like:

DECLARE @ServerName sysname,
        @Command    NVARCHAR(4000),
        @CommandTemplate NVARCHAR(4000);

DECLARE @Results TABLE ([ResultID] INT IDENTITY(1, 1) NOT NULL, [Result] NVARCHAR(4000));

SET @CommandTemplate = N'SQLCMD -S {{SERVER_NAME}} -E -h-1 -Q "PRINT @@VERSION;"';

DECLARE srvrs CURSOR LOCAL READ_ONLY FAST_FORWARD
FOR   SELECT [ServerName]
      FROM ServerStats;

OPEN srvrs;

FETCH NEXT
FROM  srvrs
INTO  @ServerName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  SET @Command = REPLACE(@CommandTemplate, N'{{SERVER_NAME}}', @ServerName);

  INSERT INTO @Results ([Result])
    EXEC xp_cmdshell @Command;

  -- Get results via SELECT [Result] FROM @Results ORDER BY [ResultID];
  -- Do something with the data in @Results

  DELETE FROM @Results;

  FETCH NEXT
  FROM  srvrs
  INTO  @ServerName;
END;

CLOSE srvrs;
DEALLOCATE srvrs;

And it wouldn't hurt to throw in a TRY / CATCH in there :-).

Even if not the most ideal of solutions, it is at least doesn't require adding 112 Linked Servers, and is dynamic and will adjust to servers being added and removed.

Upvotes: 1

John S
John S

Reputation: 33

In SQL Server you are able to create a Linked Server that you can query from another server.

On the server you wish to write the query in:

Open the Object Explorer
Go to Server Objects
Right Click Linked Servers and add a New Linked Server

Add the Name of your networked server, select SQL server and make sure to define security roles.

Upvotes: 0

Related Questions