INF: No Results From SQLDescribeParam or SQLProcedureColumns

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

The Microsoft SQL Server ODBC driver may sometimes return an empty result set from calls to SQLDescribeParam() or SQLProcedureColumns().

MORE INFORMATION

If an application calls SQLDescribeParam() or SQLProcedureColumns() while it references a procedure which is not in the current database, these functions return an empty result set to the application. SQLProcedureColumns() returns SQL_SUCCESS and SQLDescribeParam() returns SQL_NO_DATA_FOUND.

SQLProcedureColumns() returns the correct information if the szProcQualifier and szProcOwner parameters are correctly supplied with the database and owner name for the stored procedure. The application can also get the column information if it first switches the current context to the database the procedure is in by either:

• Executing the Transact-SQL USE command to switch to the database

  -or-

• SQLSetConnectOption(hdbc, SQL_CURRENT_QUALIFER, "databasename");

The most common use for these API functions to receive information about a procedure in another database is when executing the SQL Server system stored procedures (such as sp_who, sp_help, etc.). These procedures are in the master database, while most applications are executing in the context of a user database. To receive information about the parameters and columns of the system stored procedures, either fully qualify the procedure name, or use the methods mentioned above to switch the application’s current database to master. The application can switch its context back to the user database after it has the column information on the system stored procedure.