0

I am creating a new database which will connect to several databases via linked servers. The problem I am trying to solve is that I have development, test and production environment with different linked server names and I also want to prevent future rewriting of queries if some linked server will move or renamed.

What is the best practice?

My Idea is to make a constant table which will hold linked server names and create function with select for every "remote" table which will call the linked server via dynamic SQL. But the problem is that i don't want to be rewriting all the columns from remote tables to each functions.

Variant 1

Function example (but i want to avoid table definitions in return statement)

CREATE FUNCTION dbo.fnDatabase1_GetTable1()
RETURNS @ret TABLE (col1 INT)
AS
BEGIN

DECLARE @LinkedServer NVARCHAR(255) = (SELECT Value FROM dbo.Constant WHERE Name = 'LinkedServer1')
DECLARE @Query NVARCHAR(MAX) = 'INSERT INTO @ret SELECT * FROM ' + @LinkedServer + '.Database1.dbo.Table1'
EXEC(@Query)

END

Variant 2

I was also thinking about openrowset, but the linked server argument cant be passed as a parameter :/

SELECT * 
FROM OPENQUERY ([Server1\Instance1], 'EXECUTE [Database1].[dbo].[spDatabase1_GetTable1]')

Variant 3

The other elegant way could be with the view with cross apply statement, for example but I am not able to get linked server as a parameter to the FROM statement and view does not allow dynamic SQL:

CREATE VIEW dbo.vwDatabase1_Table1
SELECT Constants.LinkedServer
FROM Table1
CROSS APPLY (
    SELECT Value as LinkedServer FROM dbo.Constant WHERE Name = 'LinkedServer1'
) Constants

so it seems for me that I am trapped, what is the way you do it?

PS: Can the SQL Server project in Visual Studio help me with this problem ?

  • 2
    Two other options, 1) Create synonyms for all the objects in the remote databases and only query those. 2) Create new linked servers, which are the same on all the servers. Which is the option I would go for. – Spörri Jul 19 '16 at 9:46
  • 1
    Another option is a CLR table valued function that queries remote tables and accepts linked server name as a parameter – spaghettidba Jul 19 '16 at 10:05
  • Use dynamic sql and pass in the server name? – Jason B. Jul 19 '16 at 12:49
  • 2
    Second for @Spörri suggestion. Create a linked server with the same name in each environment, but pointing to the correct server in that environment. Your other mentioned solutions are fragile, complicated and require high maintenance. – Jonathan Fite Jul 19 '16 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.