Re: Newbie problem: Saving a view from a linked server won't work
From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 10/23/04
- Next message: Matt Calhoon: "RE: Sql Server Agent not starting due to SQL MAIL"
- Previous message: Derrick Leggett: "Re: SQL Server Birth Date!"
- In reply to: arch: "Newbie problem: Saving a view from a linked server won't work"
- Next in thread: arch: "Re: Newbie problem: Saving a view from a linked server won't work"
- Reply: arch: "Re: Newbie problem: Saving a view from a linked server won't work"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 23 Oct 2004 22:17:53 +0000 (UTC)
(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
>
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
>
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
>
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
-- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: Matt Calhoon: "RE: Sql Server Agent not starting due to SQL MAIL"
- Previous message: Derrick Leggett: "Re: SQL Server Birth Date!"
- In reply to: arch: "Newbie problem: Saving a view from a linked server won't work"
- Next in thread: arch: "Re: Newbie problem: Saving a view from a linked server won't work"
- Reply: arch: "Re: Newbie problem: Saving a view from a linked server won't work"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
