INF: ODBC Catalog Functions in Static Cursors

MORE INFORMATION

The section "Additional ODBC SQL Server Driver Information" in the SQL Server 6.5 "Programing ODBC for SQL Server" manual states that the ODBC catalog API functions can be called while using a static server cursor. Users who know that the Microsoft SQL Server Driver implements the ODBC Catalog API functions as calls to SQL Server catalog stored procedures (for example, SQLTables was implemented as a call to sp_tables) may assume this means that static cursors can be opened on the catalog stored procedures.

However, this is not a correct assumption. SQL Server 6.5 still has the restriction that server cursors can only be opened for a single select statement, or an execution of a procedure which contains only a single select statement. This prevents opening server cursors against the catalog stored procedures, because the procedures contain more than a single select statement. What the sentence in the manual is saying is that the ODBC Catalog API functions have been changed so that they can be called when the application has set its statement or connection options to use static cursors.

In SQL Server 6.5, the implementation of the ODBC catalog functions was so that the Microsoft SQL Server ODBC Driver driver does not open a cursor directly on the underlying catalog stored procedure using a static cursor. It instead:

1. Creates a temp table with the same format as the output of the procedure.
2. Uses the new 6.5 feature allowing an insert command to populate a table with the results of an execute of a stored procedure to put the catalog stored procedure results into the temporary table (more details can be found in the SQL Server 6.5 "Books Online," What's New in SQL Server 6.5, Part 4 What's New in Transact-SQL, Statements and Functions, INSERT Statement.
3. Opens a static cursor based on a select of the temp table.
4. Drops the temp table (the static cursor will still have the contents of the select).
5. Processes further application requests for the catalog information by referencing the static cursor.

Please note that because calling the catalog stored procedures using a static cursor entails creating a temporary table, doing this within a transaction will require the server to place locks on some system tables in tempdb. Users can therefore introduce locking contention on the tempdb system tables if they have a lot of ODBC clients calling the ODBC catalog functions using static cursors within transactions.