How to query against multiple linked servers?

Question

After linking some SQL Server 2008 Servers / instances, I would like to do a more generic query against these servers. I know that I must specify the destiny of the query like that:

select * 
from [SRV\INSTANCE].dbname.dbo.foo

But, I would run this query against more than one linked server. I know also that this select statement returns exactly the SRV\INSTANCE that I need:

select ss.name 
from sys.servers ss 
where ss.server_id > 0

This one, returns all servers\instances from where I want query against to.

At this scenario, all databases have the same structure, so I wanted to do something like this:

select * 
from [select ss.name from sys.servers ss where ss.server_id > 0].DBNAME.dbo.foo

Any ideas?

Thanks in advance.

performance is going to be bad. You may want to consider a Stored procedure that runs on a job/event which populates a temp table with the data of interest. That way the heavy lifting is done in advance of the query and you're now doing a select across 1 database instead of many. — xQbert, Feb 18 '13 at 21:17
Thank you @xQbert for the advice. At my scenario, I need to "one-way" synchronize data between linked servers (I know that a merge replication could do that with ease, but here I have a narrow-minded network admin =p ). I need to sync around 30 tables from multiple servers to a main server (almost two minutes of sql processing for each server). I'll try using temp tables. Thank you. — cezarlamann, Feb 19 '13 at 12:46

Aleksandr Fedorenko · Accepted Answer · 2013-02-18T22:30:54.993

6

You can dynamically create SQL statement on the fly and then run that command. In this scenario in @dml variable with help += operator the whole command dynamically is created

DECLARE @dml nvarchar(max) = N''
SELECT @dml += 'UNION ALL SELECT * FROM ' + QUOTENAME(ss.name) + 
               '.[DBNAME].[dbo].foo ' 
FROM sys.servers ss
WHERE ss.server_id > 0

SELECT @dml = STUFF(@dml, 1, 10, '')
EXEC sp_executesql @dml

edited Feb 18 '13 at 22:30

answered Feb 18 '13 at 22:06

Aleksandr Fedorenko

16,594
6
37
44

Andomar · Answer 2 · 2013-02-18T21:29:18.997

1

That requires a dynamic query, like:

declare @servers table (name sysname)

insert  @servers
        (name)
select  name
from    sys.servers
where   server_id > 0

declare @query nvarchar(max) = ''
while 1=1
    begin
    declare @server sysname

    select  top 1 @server = name 
    from    @servers

    if @@rowcount = 0
        break

    if @query <> ''
        @query = @query + ' union all ' + char(13) + char(10)

    set @query = @query + 
        ' select * from ' + quotename(@server) + '.dbname.dbo.foo ' +
        char(13) + char(10)

    delete  @server
    where   name = @server
    end

print @query -- For debugging
exec (@query)

edited Feb 18 '13 at 21:29

answered Feb 18 '13 at 21:17

Andomar

232,371
49
380
404

Thank you @Andomar for the answer. Let me understand (I've never worked with dynamic queries before :) ), will this query return X tables "foo" from X linked servers, like: X1.foo X2.foo ... Xn.foo ? – cezarlamann Feb 19 '13 at 12:51

score 0 · Answer 3 · answered Feb 18 '13 at 21:21

I have done some work where I have had to join results from two linked servers. One of the linked servers is to a redbrick database and to make a long story short, I had to use openquery.

The approach that I used was to create temporary tables in ms sql. Then I populated them with the results from the openqueries to the linked servers and used normal tsql to put it all together.

How to query against multiple linked servers?

3 Answers3

Linked