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.

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito