Unanswered: "Communication link failure" error when running a distributed query
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.
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.
Please note that this stored procedure worked before on TestMachine2 (but with less than 10,000 rows) and that connectivity is proven among TestMachine1 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.
Since the error messager starts saying "SQLNCli....TCP Provider...", would it be useful to provoke that the communication is made with Named Pipes instead of TCP/IP ? That is made by make its order number lesser than TCP/IP in the Client Protocols of the SQL Native Client Configuration node in the SQL Server Configuration Manager console, right?
I have read that Name Pipes are more suitable when the involved are in the same LAN; could you please tell me the pros and cons of making this change of protocols?
But remember that what I am really trying is avoid the error "SQLNCli. TCP Provider: network name is no longer available - communication link failure." as described in my previous post...