[DB-SIG] Simple query failure to MS SQL Server Database using mx.ODBC.Wind ows

M.-A. Lemburg mal@lemburg.com
Fri, 04 Apr 2003 18:09:51 +0200


Geter, Frank wrote:
> I am trying to use mx.ODBC.Windows connect to a remote MSSQL database
> through my local ODBC data object called OipNcapsData.  I am running Windows
> 2000 and I downloaded and installed the mxODBC files yesterday.  I can
> connect to the database using the same object and successfully run queries
> against it using other tools.  I have checked with the database people and I
> have permission on the view and tables behind it that I am trying to access.
> The following is the python code that shows the error.  After that code is
> more code showing another python odbc connection using the same ODBC object
> that works.
> 
> Code that does no work
> ----------------------
> Python 2.2.1 (#34, Apr  9 2002, 19:34:33) [MSC 32 bit (Intel)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
> 
>>>>import mx.ODBC.Windows
>>>>db=mx.ODBC.Windows.DriverConnect('DSN=OipNcapsData')
>>>>c=db.cursor()
>>>>c.execute('select * from site')
> 
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> mxODBC.Warning: ('01000', 7312, '[Microsoft][ODBC SQL Server Driver][SQL
> Server][OLE/DB provider returned message: New transaction cannot enlist in
> the specifie
> d transaction coordinator. ]', 4606)

Please note that unlike the simple win32 odbc module, mxODBC defaults
to transactional mode after a connect. The odbc module goes into
auto-commit mode after a connect.

The error you are seeing seems to be related to some transaction
manager the database is using for managing transactions. Since
the odbc module does use transactions per default, you don't get
the error messages.

The work-around is easy: use the

Fortunately, mxODBC offers to a way to connect in auto-commit
mode as well:

   DriverConnect(DSN_string[, clear_auto_commit=1])

     ...
     If you get an exception during connect telling you that the driver is not capable or does not support transactions, 
e.g. mxODBC.NotSupportedError: ('S1C00', 84, '[Microsoft][ODBC Excel Driver]Driver not capable ', 4226), please try to 
connect with clear_auto_commit set to 0.

db = mx.ODBC.DriverConnect('DSN=OipNcapsData', 0)

should give you the same results as with the odbc module.

However, what you really want is to use transcations (since
they prevent data corruption), so you should ask your DBAs
about the cause of the problem.

> Code that works
> ---------------
> 
>>>>import odbc, dbi
>>>>db=odbc.odbc('OipNcapsData//')
>>>>c=db.cursor()
>>>>c.execute('select site_id from site where site_id < 25')
> 
> 0
> 
>>>>c.fetchall()
> 
> [(1,), (2,), (3,), (4,), (5,), (6,), (7,), (9,), (10,), (11,), (12,), (13,),
> (14,), (15,), (16,), (17,), (18,), (19,), (20,), (21,), (22,), (23,)]
> 
> 
> 
> Frank Geter
> 2150 Centre Avenue, Bldg. A
> Fort Collins, CO 80526-1891
> Ph# (970) 295-5536
> Fax# (970) 295-5414
> 
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Apr 04 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
EuroPython 2003, Charleroi, Belgium:                        81 days left