Easysoft Data Access

Why are my output bound parameters from a MS SQL Server procedure not retrieved?

Article:
00988
Last Reviewed:
27th November 2008
Revision:
2

Assuming your application was written correctly then the likelihood is you are not calling SQLMoreResults() after each SQLExecute. A quote from Microsoft's web site says:

"For SQL insert statements, ODBC 3.5 changed the behavior with respect to SQLMoreResults such that, output parameters aren't stored in the application's buffer until after the app calls SQLMoreResults and it returns SQL_NO_DATA_FOUND. The ODBC 2.65 driver would read-ahead and sometimes lump result sets together or skip over them. The ODBC 3.5 driver was changed to provide result sets in a consistent fashion w/o the various problems that used to occur."

So if you have procedures that return values and they contain insert statements you must call SQLMoreResults() to fill your output bound parameters.

You may also get a function sequence error if you attempt another SQLExecute call before SQLMoreResults() has returned SQL_NO_DATA_FOUND.

According to Microsoft, setting "SET NOCOUNT ON" in your SQL will also work. However, if you do this you need to insert it into the first line of your procedure (it is no good putting it in to the SQL that calls your procedure in the SQLPrepare).

If you are using unixODBC there was a bug in an early 2.2.4 release (dated around Jan 20 2003). If SQLMoreResults returned SQL_ERROR then all further calls to SQLMoreResults were not passed through to the driver and returned SQL_NO_DATA. This prevents the OOB client from obtaining the procedure return output parameter.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.