Easysoft Data Access

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

Last Reviewed:
27th November 2008

Assuming your application was written correctly, the likelihood is that 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 into the SQL that calls your procedure in 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 Easysoft ODBC-ODBC Bridge client from obtaining the procedure output parameter.

Applies To

Knowledge Base Feedback

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

(* Required Fields)