[egenix-users] Re: [egenix-support] SQL Server connection to Oracle Bug (Distributed Database)

[eGenix.com Support Team: M.-A. Lemburg]

Remy Pinsonnault wrote:
> We are using mxODBCZopeDA-1.0.8 to connect to a SQL Server. This server is
> distributed and has a connection to an Oracle 9 database.
> 
> With the SQL Server connection, I can query the oracle tables from SQL's
> Data Analyzer or from Power Builder (with a SQL Server ODBC).
> 
> I even created an ODBC connection with the old Z ODBC Database Connection
> and the query worked fine.
> 
> But when I do the same query with mxODBC, I get the following error:
> 
> Valor del Error 
> ('42000', 7391, "[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.", 5923) 
> 
> Any idea how to fix that?

Please make sure that you switch off connection pooling for
the used connection in the MS ODBC manager (mxODBC Zope DA
has its own connection pooling - unlike the old Z ODBC
product).

Other than that, the following MS KB articles may be of help:

* How to set up and troubleshoot a linked server to Oracle in SQL Server
  http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106

* Information about using Oracle with Microsoft Transaction Server and COM+ components
  http://support.microsoft.com/kb/193893/EN-US/

* Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
  http://support.microsoft.com/kb/q244661/

Note that you may be better off with the Oracle ODBC driver,
since MS does not seem to be interested in updating the
MS Oracle ODBC driver anymore.

Regards,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Mar 29 2005)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::