Alternative to Creating View with Union in two databases?

I attempted to create a view in SQL Server 2000 that Unions two
queries. The first part of the query gets data from the local server,
the second part gets info from a linked server. (The query works fine
in Query Analyzer.)

I received this error when I tried to save the query:

ODBC error: [Microsoft][ODBC SQL Server Driver] The operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.

[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB provider
returned message: New transaction cannot enlist in the specified
transaction coordinator.]

After a little reading I discovered the "Database limitation":
"A view can be created on a table only in the database the view
creator is accessing".

That's my problem... is there a simple solution or alternative to
creating a view?

Thanks,
Mattmattd@.semiconductor.com (Matt D) wrote in news:7bd0381c.0310070503.4d416d94@.posting.google.c om:

> That's my problem... is there a simple solution or alternative to
> creating a view?

A stored procedure will likely do the trick.

--
Ross Presser -- rpresser AT imtek DOT com
... seeking a new quote ...|||Matt D (mattd@.semiconductor.com) writes:
> I attempted to create a view in SQL Server 2000 that Unions two
> queries. The first part of the query gets data from the local server,
> the second part gets info from a linked server. (The query works fine
> in Query Analyzer.)
> I received this error when I tried to save the query:

I had no problems to create a view spanning two servers.

Since you talk about saving query, I get the idea that you might
be using Enterprise Manager for the task. Try running the CREATE VIEW
statement from Query Analyzer instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp