August 6, 2003 at 6:53 am
What's the difference between a remote server and a linked server?
Why would I choose one in preference to the other?
Thanks.
August 6, 2003 at 7:04 am
use Linked Servers.
I suggest using remote servers for backwards compatibility only.
I'd offer more, but it may show up just how little I know about linked/remote servers.
Cheers,
- Mark
Cheers,
- Mark
August 6, 2003 at 7:15 am
Remote servers are set up in pairs. To set up a pair of remote servers, configure both servers to recognize each other as remote servers. Then, verify that configuration options are set properly for both servers so that each instance of SQL Server allows remote users to execute procedure calls.
A linked server configuration allows SQL Server to execute commands against different OLE DB data sources on different servers.
August 6, 2003 at 7:26 am
OK. Let's test you out on linked servers.
I have two servers and server #2 is a linked server on server #1. Some procedures on server #1 call procedures on server #2 to get various pieces of data. However, server #2 is not always available (mostly due to network problems) and I want the procs on server #1 not to execute the procedures on server #2 when it is not available.
To check whether it is available, I have a table which contains the server name and the status. The proc on server #1 checks this table - if the status is OK then it executes the server #2 proc but if it is not available then it bypasses that part of the proc (it's not essential for the proc so I want it to continue rather than return an error message).
This table is updated every minute - I have a job which pings server #2 and updates the status as appropriate.
However, recently I had a situation where server #2 was up and running SQL 2K but whenever I ran the proc on server #1 it failed with a network communication error. The only solution was to recycle the SQL Service on server #1. However, users were getting an error message until I had recycled the service.
What I plan to do, is rather than ping the other server, I would execute a select statement such as:
select top 1 name
from [server #2].[master].[dbo].[sysobjects]
If I get a row back then it's all OK (set status to OK) otherwise there is a problem (set status to Not OK).
However, whenever I execute this query I get the message:
Msg 7405...
Heterogeneous queries required the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection.....
If I execute the exact query in QA then it works fine, but as I might have more than one server to check I have set it up as a loop. For each server I create the string and then execute it with sp_executesql and this is when I get the error.
What am I doing wrong and how do I fix it?
Thanks.
Jeremy
August 6, 2003 at 7:57 am
August 6, 2003 at 8:08 am
That's fixed it.
I had to recreate the stored procedure on server #1 in QA rather than in EM.
Thanks.
Jeremy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply