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

[Remy Pinsonnault]

Thanks Marc-Andre.

I am not using Microsoft Oracle ODBC driver, neither Oracle ODBC driver but
the SQL Server ODBC that comes with XP. 

I then connect the egenix Zope DA to this SQL Server ODBC.

I created the SQL Server ODBC with default settings and I don't see anything
like connection pooling in the configuration?!

Other ideas?

Thanks

Remy Pinsonnault
Systems Analyst
Ministry of Energy and Mines, Peru

-----Original Message-----
From: eGenix.com Support Team: M.-A. Lemburg [mailto:support at egenix.com] 
Sent: Martes, 29 de Marzo de 2005 12:50 p.m.
To: remy_pinsonnault at roche.ca
Cc: egenix-users at lists.egenix.com
Subject: Re: [egenix-support] SQL Server connection to Oracle Bug
(Distributed Database)


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 ! ::::