Problem when using linked server

Hi everyone,

I've got problem querying remote tables via a specific linked server.
The server from which I execute the query is an SQLServer2005 and the linked server is an SQLServer2000.

If I do select * from <linked server>.<database name>.<db owner>.<Table1> after a while I get the following error message
"Server: Msg 10054, Level 16, State 1, Line 0, TCP Provider: An existing connection was forcibly closed by the remote host."

I execute the query using profiler and I got the following profiler error: 'OLE DB provider "Unknown" for linked server "(null)" supported the schema lock interface, but returned "0x80040e96" for "ReleaseSchemaLock".' (The query returns a subset of records before it is forcibly stopped)

The strange thing is that i used to run the same query a week or two ago and faced no problem.

Please help.

Hey Kilo

I have had this problem before also. I think its a bug in SQL 2005. Although I have not tried it yet, I think SP2 fixed the issue. Give that a shot.

|||See this blog http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx that refers the issues.|||

Hey Satya,

Thank u for your reply.

The link tha u sent me explains thye problem / posiible solution for sql server 2005 + Windows Server 2003. However in our case sql server 2005 is installed on a Windows 2000 operating system. Here is another clue:

Using profiler to map communication between sql server 2005 and sql server 2000 we get the following rows:

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0 0 0 776 68 2007-02-13 23:44:55.530 2007-02-13 23:44:55.530 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
User Error Message Changed database context to 'msdb'. SQLAgent - Step History Logger Administrator OPEN24\Administrator 2804 67 2007-02-13 23:44:58.890

User Error Message Changed language setting to us_english. SQLAgent - Step History Logger Administrator OPEN24\Administrator 2804 67 2007-02-13 23:44:58.890

When is sp_reset_connection exececuted?

Is there another parameter / event that we have to map in order to find the source of the problem?

|||It is an undocumented Proc that's used internally by SQL. You usually see it when connection pooling is being used--SQL Server uses it to reset the connection options and settings before reusing the connection.