How to replicate changes to linked server views

[fredscuba]

Hi,

How do I set up a view in a linked server to update automatically whenever the table that it is pointing to on the linked server changes in structure.

For example, my view to a table on the linked server works fine, until the table that it is pointing to is changed, for example a new column is added.

All of a sudden the view is wrong, since it no longer does it's query correctly.

Help !

fredscuba

 

[mrdenny]

You can't. You need to update your view defination manually each time the table is changed.

If you want something that updates manually, you would need to do a select t1.* from table t1 within a stored procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(My very old site)

 

[fredscuba]

You wrote "If you want something that updates **manually**, you would need to do a select t1.* from table t1 within a stored procedure.
Denny

Did you mean automatically? Because that is what I am looking for. How would doing a select from within a stored procedure help me?

What I want is a way to set up a view to a table on a linked server and to have that view be updated whenever the table it is pointing to changes in structure.

Isn't there some way to replicate the table structure changes to a linked server?


fredscuba

 

[masds]

In general, it doesn't matter the view is built on a table in a linked server or local server, as long as the defination for the table is changed, the view need to be recreated. However, in some special cases it may not be neccessary, it really depends on your specific situation, can you post the defination of your table and the view? we probably can give you a solution to your need.

 

[mrdenny]

Yes I meant automatically if you put the select t1.* within the procedure.

Using a stored procedure would be another way of retreiving the data. If wouldn't help you with reguard to automatically updating a view.



Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(My very old site)

 

[fredscuba]

Denny,

So what you are saying is that I could query the table on the linked server from inside a stored procedure, *BUT* that it still would not solve the problem of a structure change on the table that is accessed remotely?

masds - You asked for a defination of my table and the view. Well, it is just a table on a linked server with the usual stuff - an id column, some character columns and some integer columns.

The view is straightforward - it is just

CREATE VIEW TableName
AS
select *
from [Server\Instance].DBName.dbo.TableName

I am using the name of the table on the linked server as the name of the view, so that to a user it appears that they are querying the table directly out of the database that is linked and has the view in it.

I hope that helps. It really should be easy to replicate changes to tables - either through using a copy of the table and something like a publishing server and a receiving server, or by using views, like I have above. I just don't know how.

I would appreciate any ideas.

fredscuba

 

[miq]

HI,
The easiest way would be to add alter view statement at the end of the batch that contains alter table statement. Here as soon as you change structure you get the updated view.

B.R,
miq