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?
+1
A:
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.
Ben Hoffstein
2008-10-09 22:23:54
No prob. Looks like you already found it!
Ben Hoffstein
2008-10-09 22:30:45
Yeah -- it's one of those things where I figured the answer was out there, just wanted to store the howto in stack overflow :)
kurious
2008-10-09 22:34:47
+3
A:
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
kurious
2008-10-09 22:24:05
+4
A:
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:
Pittsburgh DBA
2008-10-09 23:23:52