[egenix-users] mxODBC and Microsoft SQL Server 2000

nwingfield at che-llp.com nwingfield at che-llp.com
Mon Sep 29 17:28:12 CEST 2003


We have achieved a temporary working solution by the following means:
1.  Enabled "Close cursor on COMMIT" setting in SQL Server setup to comply
with SQL 92 standards
2.  Replaced Microsoft's SQL Server driver with an alternative from
DataDirect
3.  Checked "Use Auto-Commit" on the mxODBC properties tab
4.  Reduced connection pool size to 1 on the mxODBC properties tab
5.  Disabled connection pooling in ODBC setup on the Windows server itself

The service pack had no effect on the problem.

There are two problems with this setup.
1.  We do not wish to use auto-commit
2.  We must be able to increase the connection pool size to a greater
number

However, if we disable "Use Auto-Commit," the following error is prone to
occur:
...
  Module Script (Python), line 4, in getPrevQuantities
   - <PythonScript at /earthfare/customers/getPrevQuantities used for
/earthfare/customers/ath_fs>
   - Line 4
  Module Shared.DC.ZRDB.DA, line 428, in __call__
  Module Products.mxODBCZopeDA.ZopeDA, line 1330, in query
  Module Products.mxODBCZopeDA.ZopeDA, line 1214, in run_cursor_callback
  Module Products.mxODBCZopeDA.ZopeDA, line 1329, in <lambda>
  Module Products.mxODBCZopeDA.ZopeDA, line 884, in errorhandler
ProgrammingError: ('HY000', 0, '[DataDirect][ODBC SQL Server driver]You
cannot have more than one statement active, when SQL_AUTOCOMMIT is false.',
5911)

Furthermore, when using a connection size greater than 1, we found that
lock escalation in SQL Server would prevent even a single user from
executing certain pages.  Zope would spin indefinitely, never rendering the
page because of competing database locks.  Perhaps this could be remedied
by revisiting our design?


More information about the egenix-users mailing list