Named Pipe Provider/Linked Server Connection Error

Hello, everyone!

I'm currently having an issue with a linked server. Here's the surrounding information:

A) I have a clustered SQL Server 2005 Instance (A) and a SQL Server 2000 instance (B).

B) There is a linked server on A to B. When I set it up, I did run the fix to ensure A could talk to B (There was an issue with communication between 2005 and 2000 servers). It has been there since I installed A, and has worked fine.. Until last week.

C) This linked server uses static credentials to connect to B.

D) Named Pipes are enabled on both servers to listen to both connections on both A and B in cliconfg. So A has a named pipe listening for B, and B has a named pipe listening for A.

E) A has a view that looks at a table on B - It's a table view, very simple, just pulls in all the data from the table on B.

So, I go to do a select statement from the view, connected as a user other then 'sa'. I then get the error:

Named Pipes Provider: Could not open a connection to SQL Server Linked Server

Error Source: ncli Client (Paraphrasing, didn't copy that down, but it was the ODBC connector)

Well, that's odd. It's been working fine for months now..

I then go and connect as sa on A to query B. It works! Mind you, absolutely NO QUERY from A to B will run again until I run SOME kind of query has been run as sa on A.

Mind you, this is not a credential issue. Every user who connects to the linked server to B uses a stored credential that is DBO to the database on B. The same User Name and Password exists on both A and B.

Also of note, I check the activity monitor. There's a process that is "dormant" every time I run a query against the view. The details of this connection are:

sp_reset_connection;1

My question is, why is it resetting the connection on B when being queried from A? Why is it "all of a sudden" a problem? Are there any changes that coudl ahve been made that would cause this?

Any help with this confusing issue would be appreciated.

Thanks!

No one has any idea?

|||we found this to help us out: data source = servername,1433 (the port used), we noticed that when using an alias as the Linked Servername (not the actual servername) it failed, yet if the servername was the LS name it worked, so we added ',1433' to the data source and it cleared up our problem.