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

[Remy Pinsonnault]

Yes I have revised the URLs you mentioned and of course MSDTS is running.

With eGenix's ODBC I always get the error. In fact I can query the SQL
Server's tables fine but when I do a query on the Oracle linked server
tables, I always get the error. I repeat that the old ODBC Zope DA can query
the Oracle linked server without a problem. I also tried connecting with the
same ODBC from ASP and Powerbuilder and everything works fine.

I am using the latest ODBC for SQL Server.

Maybe I can send you off-list screen shots of my configuration?

Thanks

Rémy

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


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