[egenix-users] Exceptions with MS SQL, Stored Procedures

[M.-A. Lemburg]

Peter Sabaini wrote:
> Hello list,
> 
> 
> I am getting strange exceptions when using the mxODBC Zope DA:
> 
> """HY000 [Microsoft][ODBC SQL Server Driver]Connection is busy with 
> results for another hstmt"""
> 
> (german: """HY000 [Microsoft][ODBC SQL Server Driver]Die Verbindung ist 
> mit Ergebnissen von einem anderen hstmt belegt""")
> 
> 
> The exception only occurs under load.
> 
> Has anyone else seen this?
> 
> I'm using Zope 2.6.1 / win32, Python 2.1.3 with MS SQL Server 8.00.194 
> and MDAC 2.8; our application relies heavily on stored procedures, and I 
> turned on the "Fetch last available result set" option.

Here's a MS article on the topic:

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D253010

They say it was corrected in MDAC 2.6...

Another reference mentions it as limitation in MS SQL Server ODBC
driver:

http://www.588188.com/netbook/sqlserver2000/odbcsql/od_6_030_5337.htm

The problem only occurs if you have multiple result sets open,
e.g. from a stored procedure call.

The only true fix seems to be to have the stored procedures close
the result sets (that are not needed) before returning the final
result set or to use an ODBC driver from one of the other ODBC
driver vendors which don't have this limitation.

> The "mxODBC Interface Documentation" mentions some issues with MS SQL 
> and recommends forcing the usage of server side cursors via the 
> setconnectoption() method of the database connection. How do I do this 
> with the Zope DA? As far as I can tell, the Zope "Database Connection" 
> has an attribute 'connection', which features a setconnectoption() 
> method. I tried to set the CURSOR_TYPE to CURSOR_DYNAMIC, but that 
> didn't help, ie. the exception still occurs.

Setting these low-level connection options is currently not
advisable on DatabaseConnections because these are pooled, so
tweaking one connection will not reach out to other connections
and as a result you'll get strange effects and unreliable
connections.

You could, however, experiment with this if you set the pool
size to 1 and only have one Zope connection object in your
ZODB.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Aug 18 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2003-08-12: Released eGenix mx Extensions for Python 2.3

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::