472,110 Members | 2,292 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,110 software developers and data experts.

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,
Matt
Jul 20 '05 #1
2 8282
ma***@semiconductor.com (Matt D) wrote in news:7b**************************@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 ...
Jul 20 '05 #2
Matt D (ma***@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by trish258 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.