Selecting data from two different servers in SQL Server

Question

How can I select data in the same query from two different databases that are on two different servers in SQL Server?

Answer 1

Linked server

Answer 2

Created a Linked Server definition in one server to the other (you need SA to do this), then just reference them with 4-part naming (see BOL).

Answer 3

Hell yes you can.

I think you're asking how, so I'll answer that.

What you're looking for are Linked Servers. You can get to them in SSMS under

Server Objects-->Linked Servers

or you can use sp_addlinkedserver.

You only have to set up one, though. Once you have that, you can then call a table on the other server like so:

select
    *
from
    LocalTable,
    [OtherServerName].[OtherDB].[dbo].[OtherTable]

Note that the owner isn't always dbo, so make sure to replace it with whatever schema you use.

Answer 4

SELECT
        *
FROM
        [SERVER2NAME].[THEDB].[THEOWNER].[THETABLE]

You can also look at using Linked Servers. Linked servers can be other types of data sources too such as DB2 platforms. This is one method for trying to access DB2 from a SQL Server TSQL or Sproc call...

Answer 5

Querying across 2 different databases is a distributed query. Here is a list of some techniques plus the pros and cons:

1. Linked servers: Provide access to a wider variety of data sources than SQL Server replication provides
2. Linked servers: Connect with data sources that replication does not support or which require ad hoc access
3. Linked servers: Perform better than OPENDATASOURCE or OPENROWSET
4. OPENDATASOURCE and OPENROWSET functions: Convenient for retrieving data from data sources on an ad hoc basis. OPENROWSET has BULK facilities as well that may/may not require a format file which might be fiddley
5. OPENQUERY: Doesn't support variables
6. All are T-SQL solutions. Relatively easy to implement and set up
7. All are dependent on connection between source and destionation which might affect performance and scalability

Answer 6

sp_addlinkedserver('servername')

so its should go like this -

select * from table1 unionall select * from [server1][database][dbo].[table1]