running openquery with local database and server credentials

Question

running openquery with local database and server credentials

aaron.reese

SSChampion

Points: 13498
More actions
November 25, 2020 at 1:19 pm

#3813853
Hi guys,
I have an application that can only call views: The data it needs to access is from a stored procedure that uses temp tables so the only way to call the data is by using OPENQUERY
```
CREATE VIEW [dbo].[myView] AS
SELECT * FROM OPENQUERY(LocalServerName, 'EXEC LocalDatabaseName.dbo.mySproc')
GO
```
in OPENQUERY the Servername and Databasename need to be specified explicitly. I.e. not LocalServerName but 'SQLServer2017_DEV01'. this causes issues with DEVOPS because the view cannot be moved from DEV to TEST to PROD without being altered. More importantly the REPORT server is a copy of the PROD database from the night before and I don't want to have to run an external script to update the view each time the database is refreshed. I can get the local server from @@SERVERNAME and the local database from DB_NAME() but I can't work out how to build the view command; all the examples I can find on the net rely on the call being in a stored procedure. @@SERVERNAME seems to be OK to concatenate into a string but (SELECT DB_NAME()) gives me a syntax error. Also you can't use EXEC
I have found one way around the issue, which is to create a new linked server called LOOPBACK which is set up as Other Provider but links back to the local server: The LOOPBACK will be context sensitive to the instance it is running on
```
EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'DEVSQL01'
```
HOWEVER... There seems to be an issue when I run the command:
```
SELECT * FROM OPENQUERY(LOOPBACK, 'EXEC dbo.mySproc')
```
mySproc exists in a number of different Databases on the DEVSQL01 server but the OPENQUERY is not running in the context of the spid database, but rather in the context of the database that I was connected to when I configured the LOOPBACK linked server. If you run just the EXEC part of the OPENQUERY it of course does run in the context of the selected database. This is not currently an issue in production or reporting because there is only one copy of the database but it could cause issues in DEV.
December 10, 2020 at 7:47 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

frederico_fonseca SSCoach Points: 16033 More actions · Answer 1

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.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 2

Dr. Low wrote about this recently: https://blog.greglow.com/2017/11/06/linked-servers-dont-hard-code-server-names/

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 3

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