Query Across Multiple SQL Server Registrations

I have defined 2 SQL Server registrations in Ent Mgr. One is running locally and the other is located on another server. I want to be able to create a stored procedure in the local database that pulls data into a local table from the remote server.

I have wasted much time trying to define Linked Servers and Remote servers, and find the documentation all confusing and subsequently I have gotten nowhere (except frustrated). How do I configure the remote database so I can access it from a stored procedure in the local database?

Here is the code for creating the linked server. Run script against your local database. Hope you have access permissions on remote db. Also replace the IP address 999.999.999.999 with your remote system's IP address. You can query the tables on remote db with four part name.

Code Snippet

USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'Lnk_RemoteDB', @.srvproduct=N'sqlserver', @.provider=N'SQLOLEDB',
@.datasrc = '999.999.999.999', -- IP Address
@.catalog=N'RemoteDB'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'Lnk_RemoteDB', @.locallogin = NULL , @.useself = N'True'

|||

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

|||

Sniegel wrote:

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

select * from Lnk_RemoteDB.AgentDB.dbo.Agents|||I get an Authentication failed error. I tried switching the mode through EM and entering a username/password, but it didn't seem to work.