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.
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
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]')
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 ?