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

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.

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.

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

SYMPTOMS

CAUSE

STATUS

WORKAROUND