INF: SQL Server 6.0 ODBC Driver Changes Tempdb Usage (135532)

MORE INFORMATION

You can configure the Microsoft SQL Server driver to generate stored procedures to support the ODBC SQLPrepare statement. One of the problems with older Microsoft ODBC SQL Server drivers was that if an ODBC client's connection with SQL Server was broken for some reason, the ODBC driver would not get a chance to clean-up these procedures.

One change made in SQL Server version 6.0 is the introduction of temporary stored procedures, which are now used by the ODBC driver when it creates its procedures for prepared statements. These temporary stored procedures are automatically cleaned-up by SQL Server if a client connection is broken before the application finishes processing. This eliminates the problem with orphaned stored procedures seen with earlier SQL Server versions. The temporary stored procedures are located in tempdb, while the regular stored procedures used in previous SQL Servers were located in the user database, so you will see a change in the distribution of these stored procedures.

The current Microsoft ODBC drivers do not generate these temporary stored procedures when they are also using server cursors. The drivers do not use server cursors when the following statement options are set:

   SQL_CONCURRENCY = SQL_CONCUR_READ_ONLY
   SQL_CURSOR_TYPE = SQL_CURSOR_FORWARD_ONLY
   SQL_ROWSET_SIZE = 1
				

The ODBC driver does use server-side cursors for all other ODBC cursor types when executing a single SELECT statement, or a stored procedure that contains only a single SELECT statement. If the SQL command being executed does not fit this profile, the driver will not use server cursors.

The second change is in the driver specific SQLSetConnectOption of SQL_USE_PROCEDURE_FOR_PREPARE. The version 2.00.1912 ODBC driver had two options for this: SQL_UP_OFF, and the default of SQL_UP_ON. When SQL_UP_ON was specified, the driver would create a stored procedure when SQLPrepare was called, and then drop the stored procedure on either a SQLFreeStmt(SQL_DROP), SQLDisconnect, or on the next call to SQLPrepare.

The version 2.50.0121 ODBC driver has changed so that there are now three options: SQL_UP_OFF, SQL_UP_ON, and SQL_UP_ON_DROP. In the new driver, SQL_UP_ON_DROP behaves the way SQL_UP_ON behaved in the 2.00.1912 driver. SQL_UP_ON has had its behavior changed so that it no longer drops the generated procedures on a call to SQLFreeStmt(SQL_DROP) or the next SQLPrepare, it only drops them on a call to SQLDisconnect. SQL_UP_ON remains the default for the driver. This means some long running applications which maintain one or more connections for long periods may see a buildup of temporary stored procedures in tempdb when using the 2.50.0121 driver. The applications can eliminate the problem by calling SQLSetConnectOption to set SQL_USE_PROCEDURE_FOR_PREPARE to SQL_UP_ON_DROP instead of SQL_UP_ON. This is documented in the SQL Server 6.0 ODBC Driver help file DRVSSRVR.HLP, and in the ODBC driver chapter of the SQL Server 6.0 Books Online, "ODBC SQL Server Driver."

SQL_UP_ON and other options are defined in SQL Server ODBC driver help file as follows:

   #define SQL_UP_OFF      0L
   #define SQL_UP_ON       1L
   #defineSQL_UP_ON_DROP   2L
   #define SQL_UP_DEFAULT  SQL_UP_ON