Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

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

Assuming your application was written correctly, the likelihood is that you're not calling SQLMoreResults after each SQLExecute. A quote from Microsoft's web site states that:

"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 also 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 also works. However, if you do this, it needs to be the first line of your procedure. (It's 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, 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.