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

Question

I need to do a join across two different database servers (IPs 10.0.0.50 and 10.0.0.51). What's the best way?

score 22 · Answer 1 · answered Oct 09 '08 at 22:24

22

The solution I found:

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

answered Oct 09 '08 at 22:24

Kalid

22,218
14
44
46

score 21 · Answer 2 · answered Aug 07 '15 at 17:20

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

score 19 · Accepted Answer · answered Oct 09 '08 at 22:23

19

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.

answered Oct 09 '08 at 22:23

Ben Hoffstein

102,129
8
104
120

1

Yeah -- it's one of those things where I figured the answer was out there, just wanted to store the howto in stack overflow :) – Kalid Oct 09 '08 at 22:34

score 6 · Answer 4 · answered Oct 09 '08 at 23:23

You can, as mentioned, use sp_addlinkedserver. However, you may also do this via Enterprise Manager (2000) or SQL Server Management Studio (2005). Under the "Security" node, there is a "Linked Servers" node, which you can use to add and configure Linked Servers. You can specify security settings, impersonation, etc.

See these for SQL Server 2000:

Configuring Linked Servers

Establishing Security For Linked Servers

Configuring OLEDB Providers for Distributed Queries

See these for SQL Server 2005:

Linking Servers

Security for Linked Servers

Configuring Linked Servers for Delegation

Configuring OLEDB Providers for Distributed Queries