[egenix-users] mxODBC and Microsoft SQL Server 2000

[M.-A. Lemburg]

nwingfield at che-llp.com wrote:
> We are having trouble with mxODBC and Microsoft SQL Server 2000, when two
> or more people run the same query at the same time.  This trouble is easily
> reproduced by opening two browser windows and loading a page that executes
> a lengthy SQL query in both windows simultaneously.  Exhibit A shown below
> is the most common, but Exhibit B is not hard to reproduce under the same
> circumstances.  Exhibits C and D are also fairly common, and though not
> directly SQL-related, suggest incomplete or corrupted recordsets.
> Sometimes one window will successfully load the page; sometimes neither
> will.  Never do both windows successfully load the page simultaneously.  I
> would appreciate any suggestions.

This seems to be related to a bug in the SQLDescribeParam API
implementation of more recent MS SQL Server 2000 ODBC drivers:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321255

Upgrading to the latest SQL Server 2000 service pack should
solve the problem according to MS.

> EXHIBIT A
> Error Type: ProgrammingError
> Error Value: ('HY000', 0, '[Microsoft][ODBC SQL Server Driver]Connection is
> busy with results for another hstmt', 5911)
> 
> EXHIBIT B
> Error Type: InterfaceError
> Error Value: ('HY010', 0, '[Microsoft][ODBC SQL Server Driver]Function
> sequence error', 4350)
> 
> EXHIBIT C
> No input was provided for vendor_ids (this is a ZSQLMethod error)
 >
> EXHIBIT D
> Error Type: IndexError
> Error Value: tuple index out of range

General note: please always provide complete tracebacks when
reporting errors, e.g. exhibits C & D don't have enough information
to provide proper support.

> Some environmental variables to consider:
> - We do not use stored procedures
> - This occurs with every permutation of connection pooling options, within
> both Zope and the Windows ODBC Administrator
> - This does not occur under ZODBCDA

This is because ZODBCDA always uses SQLExecDirect and does
make use SQLDescribeParam at all.

> - Connected database: Microsoft SQL Server, 08.00.0194
> - ODBC driver: SQLSRV32.DLL, 03.80.0194

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Sep 27 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

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