Alternative to Creating View with Union in two databases?

Matt D

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

Subscribe Post Reply

8361

Ross Presser

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

Erland Sommarskog

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

by: MT | last post by:

hi all, I have a view that has data from this year as well as previous years. the select statment looks something like this: create view as select year, costs_mon1, costs_mon2, costs_mon3...

Oracle Database

Creating view (transpose records to columns)

by: Sanjay Asrani | last post by:

I have a table like the following Field1 Field2 Field3 ------ ------- ------ x1 y1 z1 x1 y2 z2 x1 y3 z3 x1 ...

Microsoft SQL Server

Need help with creating view

by: trish258 | last post by:

I need to create a view that scores a research assessment. So I have questions 1 through 10, but the problem is that Q3 and Q5-10 have to be recoded so that if the response is 3, it becomes 0,...

Microsoft SQL Server

Creating and using databases under vb.net

by: John Dann | last post by:

Apologies if this isn't the best group - maybe someone can suggest a more appropriate one - I can't immediately see anything on ado.net. I need to store a significant amount of data (say 1-10MB)...

Visual Basic .NET

Creating View in phpmyadmin

by: Shalini Bhalla | last post by:

I want to create a view in phpmyadmin , how can i ? or Can an requery a Crosstbquery in phpmyadmin ?

MySQL Database

creating view and dead lock problem(urgent)

by: vaskarbasak | last post by:

Hi All, we have a large table having 60 lakhs and more data.so we are facing a problem when we are executing insert or update query. as a result our DB is very slow and sometimes we are facing...

MySQL Database

Need help creating the union of two lists (help a noob)

by: GKRAFT | last post by:

I'm trying to create a function which would look like this when running: >> union(,) >> union(,) I have written the following code: def union(l1, l2): finalList = i = 0

Python

Having problems creating a Union Query

by: joshtate2001 | last post by:

I have been trying to figure out how to create a Union Query between two tables with different data with one field containing similiar data. I have 2 tables: NoteData & IndirectData The...

Microsoft Access / VBA

Needing Help Creating a Union Query

by: Dave Smith | last post by:

I need to take four queries and make them into one table This is what I get when I just do a simple select query. SELECT Wholesale_Rank_qry_2.OMNI_Number, Wholesale_Rank_qry_2.branch, ...

Microsoft Access / VBA

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

Alternative to Creating View with Union in two databases?

Similar topics