Selecting data from two different servers in SQL S

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

I know this is an old question but I use synonyms. Supposedly the query is executed within database server A, and looks for a table in a database server B that does not exist on server A. Add then a synonym on A database that calls your table from server B. Your query doesn't have to include any schemas, or different database names, just call the table name per usual and it will work.

There's no need to link servers as synonyms per say are sort of linking.

Answer 2

 select * 
 from [ServerName(IP)].[DatabaseName].[dbo].[TableName]

Answer 3

As @Super9 told about OPENDATASOURCE using SQL Server Authentication with data provider SQLOLEDB . I am just posting here a code snippet for one table is in the current sever database where the code is running and another in other server '192.166.41.123'

SELECT top 2 * from dbo.tblHamdoonSoft  tbl1 inner JOIN  
OpenDataSource('SQLOLEDB','Data Source=192.166.41.123;User ID=sa;Password=hamdoonsoft')
.[TestDatabase].[dbo].[tblHamdoonSoft1] tbl2 on tbl1.id = tbl2.id

Answer 4

Simplified solution for adding linked servers

First server

EXEC sp_addlinkedserver @server='ip,port\instancename'

Second Login

EXEC sp_addlinkedsrvlogin 'ip,port\instancename', 'false', NULL, 'remote_db_loginname', 'remote_db_pass'

Execute queries from linked to local db

INSERT INTO Tbl (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM [ip,port\instancename].[linkedDBName].[linkedTblSchema].[linkedTblName]

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

Server 2008:

When in SSMS connected to server1.DB1 and try:

SELECT  * FROM
[server2].[DB2].[dbo].[table1]

as others noted, if it doesn't work it's because the server isn't linked.

I get the error:

Could not find server DB2 in sys.servers. Verify that the correct server name was specified. If necessary, execute stored procedure sp_addlinkedserver to add the server to sys.servers.

To add the server:

reference: To add server using sp_addlinkedserver Link: [1]: To add server using sp_addlinkedserver

To see what is in your sys.servers just query it:

SELECT * FROM [sys].[servers]