Microsoft KB Archive/832627

= You receive SQL errors if the result set of a stored procedure is larger than 32 kilobytes =

Article ID: 832627

Article Last Modified on 4/22/2005

-

APPLIES TO


 * Microsoft Host Integration Server 2000 Standard Edition
 * Microsoft Host Integration Server 2000 Service Pack 1

-





SYMPTOMS
If Microsoft OLE DB Provider for DB2 (DB2OLEDB) calls a stored procedure that returns a result set that is larger than 32767 bytes (32 kilobytes), you may receive one of the following error messages:

Query is not opened. SQLSTATE: HY000, SQLCODE: -345

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 54001, SQLCODE: -101



CAUSE
When the DB2OLEDB provider is not in a transaction, the DB2OLEDB provider incorrectly issues an rdbcmm command immediately after the EXCSQLSTT statement to execute SQL.The rdbcmm command is a Relational Database (RDB) Commit Unit of Work command.

This behavior causes the target DB2 system to return one block of data and then to commit the statement. When the DB2OLEDB provider tries to fetch the next block of data by using the cntqry (continue query) command, the target DB2 system returns a QRYNOPRM (query not open) error because the SQL statement has already been committed.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



WORKAROUND
To work around this problem, use one of the following methods:
 * Call the stored procedure from inside a transaction. This transaction may be either a Microsoft Distributed Transactions Coordinator (MSDTC) transaction or a local ADO or ADO.NET transaction. When the DB2OLEDB provider is used in a transaction, the rdbcmm command is not issued until the calling application requests the rdbcmm command.
 * Modify the statements that are used to declare the cursor on the target DB2 system to include the WITH HOLD option. The WITH HOLD option is used to keep the cursor open even after a commit has been issued.

Keywords: kbbug kbnofix KB832627

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.