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?
Add a comment
|
4 Answers
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
Comments
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:
Comments
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.
1 Comment
Kalid
Yeah -- it's one of those things where I figured the answer was out there, just wanted to store the howto in stack overflow :)
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:
Establishing Security For Linked Servers
Configuring OLEDB Providers for Distributed Queries
See these for SQL Server 2005:
1 Comment
ESP32
In SQLServer 2012 this node has been moved to "Server Objects"