November 25, 2020 at 1:47 pm
openquery uses a linkedserver name - that name DOES NOT need to be the physical server name although when you create it through the gui and you specify it as a SQL Server linked server it defaults to the server name.
So if you setup a generic linked server that name can be used on the openquery - but in each SQL Instance it will point to a different server.
that sorts out the server issue.
One other issue you may have is if you wish to have different database names (either from server to server, or within the same instance) - for that there is no workaround within a view as dynamic sql is required to pass the query to openquery.
On this case you would need to regenerate the view on each environment - doable but requiring more work.
November 25, 2020 at 7:12 pm
Dr. Low wrote about this recently: https://blog.greglow.com/2017/11/06/linked-servers-dont-hard-code-server-names/
November 25, 2020 at 10:17 pm
Wouldn't it be easier to schedule an agent job to rebuild/populate a table in the database(s) - and have a view of that table for the application to access?
If your database is a copy of PROD from the night before...then it wouldn't be too hard to setup an agent job or add a post step to the restore process to execute the stored procedure to truncate and repopulate the table. You could even use synonyms to alternate between 2 separate tables - that way you could build the new table without impacting any users, one the new table is built - drop and recreate the synonym to point to the newly populated table...the next day you would use the previous table...and swap back once it has been populated.
The other option I see is to rewrite the code so it doesn't use any temp tables...not sure if it is possible or what kind of performance - but if it can be done and performs well enough it might be a better solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply