adodbapi - 'cannot create new connection' error

[Former user]

I run into this problem when first attempting to connect to any MSSQL 2000 database (I haven't tried 2005).

from sqlalchemy import * global_connect( 'mssql://localhost:1234/mydb' ) topics = Table( 'topic', default_metadata, autoload=True )

I'm using adodbapi 2.0.1 installed using easy_install in Python 2.5.

The exception that is thrown is the following:

Traceback (most recent call last): File "<stdin>", line 1, in <module> File "f:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\schema.py", line 166, in call metadata.get_engine().reflecttable(table) File "f:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\engine\base.py", line 809, in reflecttable self.dialect.reflecttable(conn, table) File "F:\Python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\databases\mssql.py", line 532, in reflecttable c = connection.execute(s) File "f:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\engine\base.py", line 520, in execute return Connection.executorsc(self, object, multiparams, params) File "f:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\engine\base.py", line 560, in execute_clauseelement return self.execute_compiled(elem.compile(dialect=self.dialect, parameters=param), multiparams, **params) File "f:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\engine\base.py", line 571, in execute_compiled self._execute_raw(context) File "f:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\engine\base.py", line 584, in _execute_raw self._execute(context) File "f:\python25\lib\site-packages\sqlalchemy-0.3.8-py2.5.egg\sqlalchemy\engine\base.py", line 602, in _execute raise exceptions.SQLError(context.statement, context.parameters, e) sqlalchemy.exceptions.SQLError: (DatabaseError)

---

Strategy 1: Traceback:Traceback (most recent call last): File "build\bdist.win32\egg\adodbapi\adodbapi.py", line 540, in executeHelper

adoRetVal=self.cmd.Execute()

File "<COMObject ADODB.Command>", line 3, in Execute File "f:\python25\lib\site-packages\win32com\client\dynamic.py", line 258, in ApplyTypes result = self.oleobj.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args) com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', 'Cannot create new connection because in manual or distributed transaction mode.', None, 0, -2147467259), None) ... (same error with remaining 3 strategies)

This SQL Server error is documented in their knowledge base (http://support.microsoft.com/kb/272358). The problem appears to be related to the way sqlalchemy opens connections to the database and when transactions are committed.

The problem does not exhibit itself when pyodbc is used instead (on the same machine using the same unit test), so the problem tied to adodbapi.

The exception be gotten directly from adodbapi using the following test:

import adodbapi conn = adodbapi.connect( 'Provider=SQLOLEDB;Data Source=localhost,1165;Initial Catalog=mydb;Integrated Security=SSPI' ) cur = conn.cursor() cur.execute( 'select * from topic' ) cur.execute( 'select * from topic' ) Traceback ...

It appears that re-using the same cursor raises the described exception, as the following test will not raise the exception:

import adodbapi conn = adodbapi.connect( 'Provider=SQLOLEDB;Data Source=localhost,1165;Initial Catalog=mydb;Integrated Security=SSPI' ) cur = conn.cursor() cur.execute( 'select * from topic' ) cur = conn.cursor() cur.execute( 'select * from topic' )

Another hint as to the nature of the problem - When using the sqlalchemy engine, every other call to execute on a connection will raise the error:

global_connect( 'mssql://localhost:1234/mydb' ) conn = default_metadata.engine.connect() conn.execute( 'select * from topic' ) <sqlalchemy.engine.base.ResultProxy object at 0x01C62410> conn.execute( 'select * from topic' ) Traceback ... conn.execute( 'select * from topic' ) <sqlalchemy.engine.base.ResultProxy object at 0x01C62410> conn.execute( 'select * from topic' ) Traceback ...

This led me to believe it's a garbage collection issue in adodbapi.

Sure enough:

import adodbapi conn = adodbapi.connect( 'Provider=SQLOLEDB;Data Source=localhost,1165;Initial Catalog=mydb;Integrated Security=SSPI' ) cur1 = conn.cursor() cur2 = conn.cursor() cur1.execute( 'select * from topic' ) cur2.execute( 'select * from topic' ) Traceback ...

but:

import adodbapi conn = adodbapi.connect( 'Provider=SQLOLEDB;Data Source=localhost,1165;Initial Catalog=mydb;Integrated Security=SSPI' ) cur1 = conn.cursor() cur2 = conn.cursor() cur1.execute( 'select * from topic' ) del cur1 cur2.execute( 'select * from topic' )

no problem.

This appears clearly to be a problem that is easily encountered with adodbapi. Nevertheless, with some care, it may be avoidable in SQL alchemy. I will continue to use pyodbc, but I wanted to log these experiences for posterity.

[paj]

Thanks for the report. My experience is that adodbapi has a number of problems that cannot easily be fixed. SQLAlchemy does already have a couple of workarounds in place, but with PyODBC now being the preferred connector, I don't want to spend any more time working on them. If you want to submit patches, I'll definitely consider them.