generic server/database referencing question in SQL 2K/2005... 1

[kraxmo]

I am using the same view (for 140 individual tables) across multiple copies of the same database (each with different names on 6 different servers). I need a way to generically reference a server and/or database name within a view. I am open to any technique that will work within a view and would resolve at run-time.

Example 1:
create view dbo.MyTrans
as
select * from @server.@dbname.dbo.transactions

Example 1:
create view dbo.MyTrans
as
select * from @dbname1.dbo.transactions as t
inner join @dbname2.dbo.control as c
on t.appid = c.appid

Note: I only use select * as an example; I am using named columns in the actual views.

Jim Kraxberger
Developing Access solutions since 1995
jkraxberger@scp4me.com

 

[mrdenny]

You can't use variables for the object names in a query.

Do you this, you'll have to use Dyanamic SQL (which raises security and performance issues of it's own).

declare @CMD varchar(1000)
delcare @db sysname
declare @server sysname
set @server = 'SERVER1'
set @db = 'DB3'
set @CMD = 'select * from ' + @server + '.' + @db + '.dbo.table'
exec @CMD

This would only work in a stored procedure.

Views don't accept paramaters.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com