0

If I run EXECUTE database.schema.procedure on SERVERB, the stored procedure (a non-trivial SELECT with no reference to dates or times) returns current data.

If I run SELECT * FROM OPENQUERY(SERVERB, 'EXECUTE database.schema.procedure') on SERVERA, data from 2 years ago are returned (rows which no longer exist in the main table queried).

SERVERA is running SQL Server 2012. SERVERB was running SQL Server 2012 when the stored procedure was created and was upgraded in-place to SQL Server 2019 a few months ago.

Naturally, SERVERA has been rebooted several times since. The data are more recent than the creation date of the stored procedure and much older than the upgrade of SERVERB.

If I copy the SELECT statement from the stored procedure into the OPENROWSET() statement on SERVERA, the current data are returned.

Where (and why) is SERVERA caching the results from 2 years ago?

2 Answers 2

3

Where (and why) is SERVERA caching the results from 2 years ago?

Linked Servers and OPENQUERY() don't do any sort of caching.

When you run the query SELECT * FROM OPENQUERY(SERVERB, 'EXECUTE database.schema.procedure') you're calling out a Linked Server object with the name SERVERB that exists on SERVERA. Just because it happens to be named the same name as one of your other host SQL Servers, doesn't mean it necessarily points to it.

I'd recommend running the following code to check where that Linked Server object actually resolves to:

SELECT 
    [name] AS LinkedServerObjectName,
    data_source AS ActualSourceServerName
FROM sys.servers
WHERE [name] = 'SERVERB';

If the ActualSourceServerName returned is SERVERB, then the follow up question is how do you know the data being returned is 2 years old and not the same as what's returned when running the procedure directly on SERVERB?

4
  • Thanks for your suggestion, but it's not that. I know the age of the data from a date field in the results. The records in the table in question are open Works Orders which are archived and deleted from the table when the Works Order is closed. I'll answer my own question and you can tell me if you think I should delete the question.
    – grahamj42
    Commented May 16, 2023 at 19:51
  • @grahamj42 Hard to say, your answer doesn't make a ton of sense on first read, unless you're saying the code you ran directly on SERVERB is different than the code you ran in the OPENQUERY() statement on SERVERA (i.e. they each pointed to different schemas with different copies of the procedure). This wouldn't have anything to do with your SQL Server version. And the only way I can think of it being related to the account executing the code is if some sort of Row-Level Security implementation was involved.
    – J.D.
    Commented May 16, 2023 at 22:00
  • @JD Hope my edited answer makes more sense. SERVERB is dedicated to a third-party application, so any additions I make are in a separate schema so it's clear to the third party who's responsible.
    – grahamj42
    Commented May 16, 2023 at 22:14
  • @grahamj42 I follow ya now. That's procedure schema resolution. It defaults to the schema of the procedure itself and then looks into dbo next. This is why it's important to be explicit when referencing objects in code, inclusive of their schemas. Still not 100% sure I understand though, because in either case, it should resolve the schemas in the same order, regardless.
    – J.D.
    Commented May 17, 2023 at 3:09
1

The solution to my problem was somewhat unexpected.

The stored procedure is in the schema schema on SERVERB and does not prefix the tables in its SELECT with the schema name (they are in the dbo schema).

When I execute the stored procedure directly on SERVERB using my account with dbo privileges, it reads dbo.Table1 and dbo.Table2.

When I execute the stored procedure from SERVERA using OPENQUERY() and a the account specified in the linked server which has public access to the database in qustion, it reads schema.Table1 and schema.Table2 - copies of the tables that I made in 2021 and forgot about.

I found this by logging into SERVERB from SSMS using the account used by the linked server.

Dropping the copied tables corrected the problem.

It seems unlikely that this situation has persisted for two years without my colleagues noticing; I think it's more likely that the behaviour changed with the upgrade to SQL Server 2019 and it's taken a few weeks to be noticed.

2
  • 1
    I'm embarassed. Perhaps I should delete the question.
    – grahamj42
    Commented May 16, 2023 at 20:15
  • Probably the server move meant that the users were recreated, and the default schema was changed for those users. Commented May 17, 2023 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.