Cannot create view on linked server--DTC issue?

[jsteph]

Hi all,
I have a sql-2000 server, which has a Linked Server connection to a sql-2005 server. I've created a view within the 2K server, and I can run the view just fine--all the data shows, etc. Yet when I try to *save* the view, it gives me all kinds of garbage about "cannot start a distributed transaction..."

First, I want this view to be read only, so there will be no transactions. Second, the server allows me to read the data just fine--the problem is saving the view--so I don't understand why I need a distributed transaction to save a view to the local server when it's been established that it has a valid connection to the 2k5 server.

Thanks for any help in this,
--Jim

 

[mrdenny]

SQL needs a distributed transaction to ensure that the schema of the remote objects doesn't change while it is creating the view.

If you can't start a distributed transaction that would be a MS DTC issue that will need to be resolved first.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog