View - Need Dynamic From Clause for Cross Server Select/Join

Question

View - Need Dynamic From Clause for Cross Server Select/Join

tkatona-1119058

SSC Rookie

Points: 37
More actions
February 22, 2011 at 2:38 pm

#147145

We have different servers/instances for Development and Production AND multiple servers that house our databases.
As necessary, we have views to pull in data across servers. Also execute stored procedures across servers.
Looking for a way so when a view moves from Development to Production nothing in the DDL needs to change.
Right now we have
SELECT Field1,Field2 FROM [TestSQL2\CommonTest].Notes.dbo.Note in Test
and SELECT Field1,Field2 FROM [SQL2\Common].Notes.dbo.Note in Production.
We want to make the 'From table' dynamic so the proper value is used in Dev vs Production.
We have added a reference table that will give us ServerName and DatabaseName with appropriate entries in Dev and Production.
That is in Dev, ServerName is [TestSQL2\CommonTest] and DatabaseName is Notes. While in Production, ServerName is [SQL2\Common] and DatabaseName is Notes.
We also have a function we can pass a parameter to and get back these values.
Dynamically in a Stored Procedure we can build variables and execute:
DECLARE @EnvirVar VARCHAR(MAX)
exec @EnvirVar = GetEnvironmentValues 'SQLServer', 'CommonNotes'
Set @EnvirVar = @EnvirVar + 'spName'
exec @EnvirVar @param = @value
Also, in a stored procedure we can build a select statement with dynamic Sql:
exec @EnvirVar = GetEnvironmentValues 'SQLServer', 'CommonNotes'
set @EnvirVar = @EnvirVar + 'Widget'
select @sql = 'Select count(*) as WidgetCount from ' + @EnvirVar
EXEC(@sql)
However, since in a View, you can not DECLARE any variables, I am having trouble using the same methodology used to call Stored Procs.
This is true whether simply selecting in the view, or joining to cross server tables.
Any thoughts on how to accomplish?

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

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

ChazMan SSCrazy Points: 2259 More actions · Answer 1

You could use a table valued function instead of a view. That would return a table.

On a different note, have you thought of just defining the server Aliases differently on production that on developement/test? then the alias never changes and it points to the proper server based on the the alias.

Just a thought.

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

I think Chaz is talking about "synonyms" which work quite well for this sort of thing.

Of course, the best thing to do would be to make your production and test envirionments identical so you don't have to mess around with 3 part naming conventions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Synonym is what you want. I will note that your usage of cross-server data access brings with it HORRIBLE performance consequences.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service