|
|
You're in the
sql server programming
group:Newbie problem: Saving a view from a linked server won't work
sql server programming:
Hi
Have you checked http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106 John [quoted text, click to view] <arch> wrote in message news:417a42df$1@funnel.arach.net.au... > 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 distributedtransaction. > > [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB > Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b]. > > > > Anyone know what I'm doing wrong? > >
Hi
Have you checked http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106 John [quoted text, click to view] <arch> wrote in message news:417a42df$1@funnel.arach.net.au... > 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 distributedtransaction. > > [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB > Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b]. > > > > Anyone know what I'm doing wrong? > >
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]. Anyone know what I'm doing wrong?
Hi
This one seems to imply MSDTC is not running: http://tinyurl.com/4wghd John [quoted text, click to view] <arch> wrote in message news:417a85ec@funnel.arach.net.au... > None of the stuff in that article seems to help. Same error message occurs. > > > "John Bell" <jbellnewsposts@hotmail.com> wrote in message > news:417a46c7$0$12657$afc38c87@news.easynet.co.uk... > > Hi > > > > Have you checked > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106 > > > > John > > <arch> wrote in message news:417a42df$1@funnel.arach.net.au... > >> 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 distributedtransaction. > >> > >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB > >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b]. > >> > >> > >> > >> Anyone know what I'm doing wrong? > >> > >> > > > > > >
(arch) writes:
[quoted text, click to view] > 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
None of the stuff in that article seems to help. Same error message occurs.
[quoted text, click to view] "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:417a46c7$0$12657$afc38c87@news.easynet.co.uk... > Hi > > Have you checked > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106 > > John > <arch> wrote in message news:417a42df$1@funnel.arach.net.au... >> 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 distributedtransaction. >> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b]. >> >> >> >> Anyone know what I'm doing wrong? >> >> > >
Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager seems to prevent the error from occurring. Damn, I wish I'd thought of that! [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns958C2C52D211Yazorman@127.0.0.1... > (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
Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server... -- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org [quoted text, click to view] <arch> wrote in message news:417a42df$1@funnel.arach.net.au... > 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]. > > > > Anyone know what I'm doing wrong? > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |