At the company I work for, we currently are running 3xSQL 2005 servers and 1xSQL 2000 server. All of the 2005 servers can communicate with each other, however they cannot communicate with the 2000 server. On the reverse side, the 2000 server can communicate with all 3 2005 servers.
When a 2005 server attempts to connect with the 2000 server, I get the following error:
OLE DB provider "SQLNCLI" for linked server "2000srv" returned message "Login timeout expired". OLE DB provider "SQLNCLI" for linked server "2000srv" returned message "An error has occurred while establishing a connection to the server. 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.". Msg 5, Level 16, State 1, Line 0 Named Pipes Provider: Could not open a connection to SQL Server [5].
I have ensured that each server is added using the stored procedure:
EXEC master.dbo.sp_addlinkedserver @server = N'2000srv', @srvproduct=N'SQL Server'
I then tested using sp_testlinkedserver. All succeeded the test except for any 2005 server attempting to connect to the 2000 server.
What suggestions and possible solutions would you recommend following to resolve this error?
System stored procedures don't get updated properly with SQL2000 SP3/SP4 You have to run instcat.sql manually. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954