June 10, 2005 at 12:48 am
Hi!
Can anybody guide me how can I pass Servername dynamically in View?
Suppose I create a view like:
Create View Myview
As
Select * from [server1].databasename.tablename
So, I want to pick server name from a table in the database or from any variable.
Pls help me...
June 10, 2005 at 1:12 am
You can't. A view is a stored static definition that contains a query to build a resultset, to use like a table.
June 10, 2005 at 3:35 am
Yes, views are static.
So here's an alternative:
You can dynamically create the view based on whenever the ServerName changes in your table: (I actually use this technique often.)
Create a stored procedure spCreateMyView that drops, then recreates the view based on a parameter (see code below). Then, if the server name is modified, a trigger on the table (or procedure that updates the table) can execute your new procedure spCreateMyView, passing in the new ServerName as a paremeter. You view is now updated with the new server's name!
/**************** spCreateMyView ****************/
If objectproperty( object_id( N'' ), 'IsProcedure' ) = 1
drop procedure spCreateMyView
go
create procedure spCreateMyView @ServerName varchar(80)
AS
declare @sql nvarchar(4000)
-- Create the syntax for your view with Server name:
-- See also "Alter View" in Books Online.
select @sql = '
Create View MyView
As
Select * from [' + @ServerName + '].databasename.tablename'
-- Drop old view
if object_id( 'MyView' ) is not null -- simple check
drop view MyView
-- Create the new view
exec sp_executesql @sql
return
go
June 10, 2005 at 5:57 am
Hi
Did you try to create partitioned views. It will automatically do the direction
Cheers
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply