Running sp_refreshview causes error for view on linked server

When I try to run sp_refreshview on a view that accesses a table on a linked
server (after that table's structure has changed), I get the following error
:
Server: Msg 7391, Level 16, State 1, Procedure vwTest, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Help!!
How do I ensure that the view on the linked server remains correct, after
the table structure of the table it references changes? I understand that
sp_refreshview will do the same as dropping and re-creating the view, but wh
y
doesn't it work on a linked server?
Also, is there a way to automate the refresh of the view after a schema
change on the table it references? Some kind of trigger on the table maybe?
But the trigger events do not include a schema change do they?
FredFred,
What happens if you right-click the view in QA and choose Edit to generate
an ALTER statement and then execute that statement?
This would retain your permissions as opposed to DROP / CREATE.
HTH
Jerry
"fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
news:DB681BC7-36C7-4FB6-A9C9-C52166A96C17@.microsoft.com...
> When I try to run sp_refreshview on a view that accesses a table on a
> linked
> server (after that table's structure has changed), I get the following
> error:
> Server: Msg 7391, Level 16, State 1, Procedure vwTest, Line 3
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Help!!
> How do I ensure that the view on the linked server remains correct, after
> the table structure of the table it references changes? I understand that
> sp_refreshview will do the same as dropping and re-creating the view, but
> why
> doesn't it work on a linked server?
> Also, is there a way to automate the refresh of the view after a schema
> change on the table it references? Some kind of trigger on the table
> maybe?
> But the trigger events do not include a schema change do they?
> Fred
>|||Thanks Jerry,
The Alter View statement works. It looks something like this:
ALTER VIEW ViewName
AS
select * from [LinkedServer\PRODUCTION].Database.dbo.TblName
However, my problem remains to automate the "refresh" of this view, every
time there is a schema change on the table (on the linked server) that it
references. Some kind of trigger based on the schema change maybe?
We have quite a few views that are based on tables on linked servers and the
person that makes the schema change to the referenced tables do not always
remember to refresh the views.
Fred
"Jerry Spivey" wrote:

> Fred,
> What happens if you right-click the view in QA and choose Edit to generat
e
> an ALTER statement and then execute that statement?
> This would retain your permissions as opposed to DROP / CREATE.
> HTH
> Jerry
> "fredscuba" <fredscuba@.discussions.microsoft.com> wrote in message
> news:DB681BC7-36C7-4FB6-A9C9-C52166A96C17@.microsoft.com...
>
>|||I was able to get the sp_refreshview to work on the linked server setup, by
configuring MS DTC (Distributed Transaction Coordinator) for network access
-
see Microsoft Article ID 329332.
I still need to find a way to automate the "refresh" of this view, every
time there is a schema change on the table (on the linked server) that it
references.
Any ideas?
Fred
"fredscuba" wrote:
> Thanks Jerry,
> The Alter View statement works. It looks something like this:
> ALTER VIEW ViewName
> AS
> select * from [LinkedServer\PRODUCTION].Database.dbo.TblName
> However, my problem remains to automate the "refresh" of this view, every
> time there is a schema change on the table (on the linked server) that it
> references. Some kind of trigger based on the schema change maybe?
> We have quite a few views that are based on tables on linked servers and t
he
> person that makes the schema change to the referenced tables do not always
> remember to refresh the views.
>
> Fred
>
> "Jerry Spivey" wrote:
>