SQL Server Performance Forum – Threads Archive
"Communication link failure" error (linked server
Hello TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. I run an stored procedure in TestMachine1 which inserts about 20,000 rows to a table in myRemoteServer and brings back a similar quantity of rows. This stored procedures take about 1.5min to complete, but no error appears (not executed within a distributed transaction). When running the same stored procedure in TestMachine2 (also SQL2005SP2), the following error appears after about 1 minute of execution (not the exact text): SQLNCli. TCP Provider: network name is no longer available… communication link failure. The stored procedure can be resumed as (the where claused has been omitted): insert into myRemoteTable select * from myLocalTable — This takes 19 seconds to finishexecute synonym_MyRemoteSP — This remote SP makes updates and inserts
insert into myLocalTable select * from myRemoteTable Please note that the stored procedure worked before on TestMachine2 (but with less than 10,000 rows) and that connectivity is proven among TestMachine2 and myRemoteServer, since I can execute "select * from synonym_MyRemoteTable" with no problems at all in the TestMachine2’s Management Studio. TestMachine1 and TestMachine2 have Windows XP Professional SP2; myRemoteServer has Windows 2003 and SQL Server 2000 SP4. All the 3 machines are in the same LAN. Can you please help me to avoid this error? Would you recommend to use Named Pipes instead?
"network name is no longer available" error is mostly it is network communication error…
Make sure your two machines connected with reliable network… And also check the following blog…
http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx#1676531
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Check the NIC drivers are upto date and relevant protocols have been configured to use with similar levels of MDAC & version levels.
http://support.microsoft.com/kb/q238949/ fyi. Also it happens if you have enabled AUTO CLOSE option on the databases. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hey guys – I am getting this exact same error and it is not two machines talking to each other, its a SSIS package trying to load data on the same box, so I’m not sure that the NIC’s would have anything to do with this – has anyone found a fix for this? its very sporadic but seems to occur when we are trying to load larger amounts of data – any help is greatly appreciated – thanks – here is my error – TCP Provider: The specified network name is no longer available.
Executing the query "execute [usp_NPRInsertFromNPRStagePrev]" failed with the following error:
"Communication link failure". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What is the service pack level on SQL? Do you see any network related or hardware related errors during this time?
See if you are getting the below error too:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.) Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi EMoscosoCam,
Can you please update that if you have resolved this issue and how? I also see your post at http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx. We are having exactly the same issue: SSIS calling usp in a job which is scheduled at the same server, W2k3, 64 bit SP2
Did anybody resolve this problem?
I’m having the same problem, and everything runs on the same machine (so I wouldn’t think it’s related to network or network drivder.
We have Win2K3 Enterprise (32-bit) with SQL 2005 with the latest SP’s.
Thank in advance!
I resolved this communication failure by doing the 3 things:
1. Re-pointed my jobs to refer to local server as "localhost" replacing its network name
2. Updated my network driver
3. Lengthened the query timeout setting Client Configuration Manager
This problem is gone now even though I don’t know which of the 3 steps acutally cured the problem. Good enough for me!
]]>