SQL query across linked servers

Question

SQL query across linked servers

Rontech10111 221

Hi All,

I have a number of linked servers.

In each server I have the same table Q_TESTS.

I would like to run t-sql script across all the servers, select * from Q_TESTS where Act = 1

How can I achieve this?

Kindly advise

Many thanks

0 comments

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Erland Sommarskog 133.7K MVP Volunteer Moderator

Run this and copy and paste the result into a query window and run it. Add filters to the query as needed. The query assumes that the linked server is up so that the Q_TESTS is in the default database.

SELECT string_agg(convert(nvarchar(max), 'SELECT * FROM OPENQUERY(') + 
                  quotename(name) + 
                  ', ''SELECT * FROM Q_TESTS WHERE Act = 1'')',
                  char(13) + char(10) + 'UNION ALL' + char(13) + char(10))
FROM   sys.servers

0 comments

Answer 2

There are two main approaches, depending on whether the servers are already linked and whether SQL Server Management Studio (SSMS) can be used.

Use linked servers with four-part names (T-SQL only)

Assuming each remote instance is already defined as a linked server (for example: LSRV1, LSRV2, LSRV3) and each has the same table Q_TESTS in the same database and schema, run a query like:

SELECT 'LSRV1' AS ServerName, *
FROM LSRV1.YourDatabase.dbo.Q_TESTS
WHERE Act = 1

UNION ALL

SELECT 'LSRV2' AS ServerName, *
FROM LSRV2.YourDatabase.dbo.Q_TESTS
WHERE Act = 1

UNION ALL

SELECT 'LSRV3' AS ServerName, *
FROM LSRV3.YourDatabase.dbo.Q_TESTS
WHERE Act = 1;

Key points from the linked-server model:

Each remote table is referenced with a four-part name: <linked_server_name>.<catalog>.<schema>.<object_name>.
All linked servers must already be created (for example with sp_addlinkedserver) and working (test with sp_testlinkedserver).

Use Central Management Server / Registered Servers (SSMS GUI)

If SSMS is available and the servers are registered in a Central Management Server (CMS) or a local server group:

In SSMS, open View > Registered Servers (or press Ctrl+Alt+G).
Under Database Engine, expand Central Management Servers (or Local Server Groups) and locate the group that contains all target servers.
Right-click the server group and select New Query.

In the query window that opens, run:

   SELECT *
   FROM Q_TESTS
   WHERE Act = 1;

SSMS executes this statement on every server in the group simultaneously.
By default, results from all servers are merged into a single grid, with an extra column showing the server name.

Multiserver result options (optional):

In SSMS, go to Tools > Options > Query Results > SQL Server > Multiserver Results to:
- Ensure Add server name to the results is enabled.
- Choose whether to Merge results into one grid or show separate grids.

Either approach lets the same SELECT * FROM Q_TESTS WHERE Act = 1 run against all servers; linked servers do it from one central instance via T-SQL, while CMS/Registered Servers does it from SSMS.

References:

Share via

SQL query across linked servers

1 additional answer

Your answer