Query Returns Data From Linked Server Despite Loss of Connection?

Question

Query Returns Data From Linked Server Despite Loss of Connection?

SJTerrill

SSCrazy

Points: 2938
More actions
January 21, 2004 at 10:48 am

#107300

We have a linked server at a remote location. SQL2K SP3. Local server SQL2K SP3. The IP connection is spotty, so we thought we'd run a quick SELECT against one of the tables every minute in a job:
SELECT MAX(CAST(REQUESTED_DATE as datetime)) as TestData
FROM [LinkedServer].[DatabaseName].[dbo].[tbl_event]
I get an EMail whenever another job fails against the same linked server. I drop to a command prompt and ping -t the IP and/or name of the foreign machine and, sure enough, I get a screenful of 'Request timed out'.
So, I drop the above code into QA and run it against the local server. I get a result. While I'm watching my ping. What gives? Is SQL Server caching some data locally?
I've done this both from my machine and the local server console (Terminal Services).
SJTerrill

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SJTerrill SSCrazy Points: 2938 More actions · Answer 1

Perhaps my original post was naive. I understand that, if what I'm observing is accurate (which I've checked against two of my associates), then SQL Server must be caching something.

My real questions are:

Why cache data from a linked server at a remote location?

Is there some way we can control this particular behavior for linked servers?

Any comments welcome (including those questioning my sanity).

SJTerrill

rstone SSCertifiable Points: 6213 More actions · Answer 2

Are you sure the connection is failing from QA? Stress related TCP/IP socket issues, for example, are typically sporadic - Q328476. Otherwise, perhaps DBCC FREEPROCCACHE might force it to refresh.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

SJTerrill SSCrazy Points: 2938 More actions · Answer 3

Thanks for the response, rstone.

I've done some additional reading on SQL Server cache behavior since yesterday (and thanks for the KB number). It looks like the alternatives aren't appropriate for the relatively low volume of data we're pushing/pulling to/from the foreign box, but I guess I'll keep studying.

We've decided to perform an INSERT on the linked server... that can't get cached. It's primitive, sure. We're cheap.

As for the QA failing, it didn't appear to. I'm at the server console with a command prompt running ping -t. Then the SELECT in QA from the server console returns a result. The concern I have about this behavior: it seems SQL Server can return cached data from linked servers without testing the connection. Splitting hairs?

BTW, sorry for posting in the wrong forum. Is there some way I can move this to Administration or General?

Regards,

SJTerrill

rstone SSCertifiable Points: 6213 More actions · Answer 4

It's good to use cached data, but only if it's not stale. It would be interesting to select from a table with a computed column based on GetDate(). The cached data would always be stale.

Did you specify "SQL Server" when you created the linked server?

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

SJTerrill SSCrazy Points: 2938 More actions · Answer 5

Well, I created the link months ago... and I've slept since then... but sysservers shows it as SQLOLEDB. So yes, it's SQL Server.

<BEAT_DEAD_HORSE>

Say, the connection went down again just a bit ago. I performed my primitive test, and it failed the query in QA once and then resumed functioning. Ping was still timing out.

</BEAT_DEAD_HORSE>

Regards,

SJTerrill

gljjr SSChampion Points: 10080 More actions · Answer 6

So more than likely TCP/IP is failing and SQL Server is using Named Pipes rather than TCP/IP. I'm pretty certain that Named Pipes is the default setting. Next time you have the connection fail try changing data on the other machine and see if you can see it. I'll be you can. If you run the client networking utility you should be able to see how it is configured.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.