Linked Server Problems

All
Background: I have three SQL servers, SRV1, SRV2, and SRV3. SRV1 is running
SQL Server 2000, and SRV2 and SRV3 are running SQL Server 2005.
I have SRV3 set up as a linked server on SRV1. I have also set up SRV3 as a
linked server on SRV2. When I run a query against SRV3 on SRV1, I never have
any problems. When I try the same query on SRV2, it fails every time, but
inconsistently. Sometimes it will simply not return any records, with no
error messages. Sometimes it will return a few records (differing amounts
each time), but also supply the following error message:
OLE DB provider "SQLNCLI" for linked server "SRV3" returned message
"Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
Sometimes there will be different error messages (sorry, I don't have them
to hand). I've googled the error messages, but not found anything specific
enough ot start me off on a strategy to address this issue.
Any assistance is very much appreciated.
Regards
Chris Marsh
Hi Chris,
Per my understanding, you created two linked servers: LINK1: SRV1->SRV3 and
LINK2: SRV2->SRV3. SRV1 is running SQL Server 2000, and SRV2 and SRV3 are
running SQL Server 2005. The problem is that LINK1 always worked fine
while LINK2 always failed with inconsistent errors.
If I have misunderstood, please let me know.
For further research, I would like to check with you the following:
1. What are the differences between SRV1 and SRV2 on communication protocol
and SQL Server service startup account?
2. Has SQL Server 2005 SP1 been installed on SRV2 and SRV3?
You can first check the Communication Protocol via SQL Server Configuration
Manager on SRV3; if it supports TCP/IP and Named Pipes, please try
configuring the communication protocol as TCP/IP on LINK2. If TCP/IP
protocol is not enabled on SRV3, please enable it and set the TCP port
under IP Address tab. I recommend that you just set it under the IPAll and
set the Enable option of IP1 and IP2 to No; then restart your SQL Server.
1. On SRV3, run "netstat -nab >C:\portstat.log" from command prompt to see
which TCP port is being listened by your SQL Server;
2. Asssuming that it is listening on the TCP port 1433, run the command
"telnet SRV3 1433" from SRV2 to see if SRV2 can telnet to the SRV3:1433; if
the telnet command fails, please check if Firewall is enabled on SRV3 by
running firewall.cpl from command prompt and add the TCP port to the
Exception list;
3. If you can telnet to SRV3:1433 from SRV2, please run cliconfg.exe from
command prompt on SRV2; switch to the Alias tab, click Add button, select
TCP/IP, input "SRV3" to the server name field, cancel the selection of
"Dynamically determin port" and input your TCP Port number to the Port
number field, and then input an alias to the Server alias field. The alias
name can be customized but I recommended that you use your SQL Server
instance full name for easy memory.
You can check your SQL Server service startup account from Service list,
right click the SQL Server service or MSSQLSERVER service, click
Properties, and switch to the Log On tab. I recommend that you use a domain
account as your SQL Server service startup account.
If your SQL Server 2005 SP1 has not been installed on SRV2 and SRV3, I
recommend that you install it before further research. You can download it
from:
http://www.microsoft.com/sql/sp1.mspx
Also, I recommend that you check if Data Access option was enabled on your
linked server. On SRV2, expand the Server Objects from SQL Server
Management Studio, expand Linked Servers, right click your linked server,
click Properties, select Server Options, and set the Data Acess option to
True.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Chris,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====