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

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

Remy Pinsonnault wrote:
> 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?

Have you checked the URLs I mentioned in the email ?

Some other things you might want to check:

* Is MSDTS running ?

* Do you always get the error below or only sometimes or
   just for some queries ?

* Does the user account you're using for Zope have proper
   permissions setup so that it can access MSDTS ?

* Are you using the latest ODBC drivers available for
   SQL Server ?

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