SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 06:13 PM
| ||||
| ||||
 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      |
|
02-28-2008, 06:13 PM
| |||
| |||
| Re: Alternative to Creating View with Union in two databases? mattd@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 ... |
|
02-28-2008, 06:14 PM
| ||||
| ||||
| Re: Alternative to Creating View with Union in two databases? 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/techinf...2000/books.asp |
Linear Mode
