How do I create and query linked database servers in SQL Server?

Solution 1:

The solution I found:

1) Run a stored proc

exec sp_addlinkedserver    @server='10.0.0.51'

2) Verify that the servers were linked (lists linked servers)

exec sp_linkedservers

3) Run the query using the format

 [10.0.0.51].DatabaseName.dbo.TableName

Solution 2:

You need to use sp_linkedserver to create a linked server.

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
 [ , [ @provider= ] 'provider_name' ]
 [ , [ @datasrc= ] 'data_source' ] 
 [ , [ @location= ] 'location' ] 
 [ , [ @provstr= ] 'provider_string' ] 
 [ , [ @catalog= ] 'catalog' ] 

More information available on MSDN.

Solution 3:

I know that the answers above are good, but wanted to share some details that I hope others will find helpful. Worth to mention is the user access part, which I think people will need help with.

set up the link:

exec sp_addlinkedserver @server='10.10.0.10\MyDS';

set up the access for remote user, example below:

exec sp_addlinkedsrvlogin '10.10.0.10\MyDS', 'false', null, 'adm', 'pwd';

see the linked servers and user logins:

exec sp_linkedservers;

select * from sys.servers;

select * from sys.linked_logins;

run the remote query:

select * from [10.10.0.10\MyDS].MyDB.dbo.TestTable;

drop the linked server and the created login users (adm/pwd)

exec sp_dropserver '10.10.0.10\MyDS', 'droplogins'; -- drops server and logins

resources:

sp_addlinkedserver

sp_dropserver

sp_addlinkedsrvlogin

sp_droplinkedsrvlogin