CnPool.exe Test Connection Pooling with Tempdb Objects

ID: Q187874

The information in this article applies to:

Microsoft SQL Server, Enterprise Edition, version 6.5
Microsoft Visual Basic Enterprise Edition for Windows, version 5.0

SUMMARY

CnPool.exe contains a sample Visual Basic project that is used to demonstrate how Connection Pooling and Prepared Statements affect the scope and duration of temporary objects within Tempdb. This sample project might be useful in understanding the scope and longevity of temporary objects with Connection Pooling and/or Prepared Statements enabled.

When connection pooling is enabled, any temporary objects that the client creates will remain until the ODBC driver deletes them or the connection is closed, and SQL Server resets all connection specific state variables.

Any temporary object a component creates in SQL Server belongs to the connection, not the component. Consequently, when the component terminates, connection pooling disconnects from SQL Server but does not close the connection. The connection remains in the connection pool and is issued to the next component as needed. Because the connection is not released, the state information is preserved and the temporary objects are visible to the next component using the connection. For example, if you create a temporary table with a component and then terminate the component, when the next component using the same pooled connection attempts to create a temporary table with the same name, the attempt fails because the temporary table already exists for that scope.

This behavior is by design.

MORE INFORMATION

The following files are available for download from the Microsoft Download Center. Click the file names below to download the files:

Cnpool.exe

Release Date: JUN-10-1998

For more information about how to download files from the Microsoft Download Center, please visit the Download Center at the following Web address

http://www.microsoft.com/downloads/search.asp

and then click How to use the Microsoft Download Center.

How to Run the Sample Application

Before running the sample application you must register ODBCw32.dll using the Regsvr32.exe utility. To do this, click Run on the Start menu. When the Run dialog box appears, type the following command and click OK:


regsvr32 <path to odbcw32.dll>\odbcw32.dll

To examine Connection Pooling in more detail, open the code sample project in Visual Basic design mode. If you get an error when you attempt to run the Visual Basic project in design mode, reselect the reference to ODBCw32.dll (Project-References).

NOTE: The code sample depends on the Pubs database by default. Make the appropriate changes in the connection string for your specific environment.

Before running the Visual Basic application, run the SQL Client utility ISQL/w. In ISQL/w, change the database to Tempdb and type the following in the query command window:


sp_who2
   select * from sysobjects order by name

Enable Connection Pooling and Disable Prepared Statements

Now you are ready to run the code sample.

Select the Enabled option in the Connection Pooling pane.

Select the Disabled option button in the Prepared Stmt pane.

In the Command text box, type a statement to create a temporary table. For example, type the following:
```
SELECT * INTO #temp FROM Employee 
```

Execute the query text in the ISQL/w query window. Notice that NO connection labeled "ODBC ConnPool" is listed.

In the Visual Basic sample application, click Open Connection and then click Execute Command.

Rerun the query in the ISQL/w query window. You should now see one connection labeled "ODBC ConnPool" and a #temp table in the listing below.

In Visual Basic, click Close Connection and rerun the ISQL/w query. You should see that the "ODBC ConnPool" connection did not disappear and the #temp table is still listed below. This is because Connection Pooling is enabled and although the connection was closed with Visual Basic, it remains active in the Connection Pool until Visual Basic is closed.

Disable Connection Pooling and Disable Prepared Statements

Close Visual Basic and reopen it with the same sample application as in the previous procedure.

Run the Visual Basic sample application and this time do not click Enabled in the Connection Pooling pane. Also, make sure that the Disabled option is selected in the Prepared Stmt pane.

Type "SELECT * INTO #temp FROM Employee" (without the quotation marks) in the Command text box.

Rerun the query in the ISQL/w window and notice that there is no ODBC ConnPool connection listed and the #temp table is not listed below.

In Visual Basic, click Open Connection and then click Execute Command.

Rerun the ISQL/w query. You will notice that the ODBC ConnPool connection is listed and the #temp table is listed below.

In Visual Basic, click Close Connection and rerun the ISQL/w query. You will notice that the ODBC ConnPool connection and the #temp table are gone. This demonstrates that no connections have been pooled and when you close the connection, they are actually closed.

Enable Connection Pooling and Enable Prepared Statements

Close Visual Basic and reopen the sample application.

Enable both Connection Pooling and Prepared Stmt and then run the previous statement (SELECT * INTO #temp FROM Employee) by clicking Open Connection and then clicking Execute Command.

Before clicking Close Connection in Visual Basic, rerun the ISQL/w query and you will notice that the ODBC ConnPool connection exists (as before), but a temporary stored procedure, #odbc#____xxxx, is listed below. This is because the statement executed was actually wrapped in a stored procedure which was created in the Tempdb and executed.

In Visual Basic, click Close Connection and rerun the ISLQ_w query. Notice that the ODBC ConnPool connection remains but the #odbc# reference is gone, unlike the #temp table earlier.

Because we use a prepared statement, the #temp table is created within the scope of a stored procedure and, though Connection Pooling is enabled, the stored procedure goes out of scope and is dropped when the connection is closed in Visual Basic. Because the #temp table created by the stored procedure only exists within the scope and duration of the stored procedure, it is also dropped. For additional information, please see the following article in the Microsoft Knowledge Base:

Q151536 INF: SQLPrepare and Temporary Stored Procedures in SQL Server

You can repeat the previous steps and experiment with different combinations of Connection Pooling and Prepared Statements. If you first execute the statement as a prepared statement and then you open a new connection (without closing the original connection) and execute the statement as a non-prepared statement, another connection is created. The #odbc# object disappears but the #temp object does not.

Conclusion

In conclusion, you can avoid some overlapping in the Tempdb that occurs during the creation of temporary tables and other state variables when Connection Pooling is enabled, if you execute the statements within a stored procedure or if you execute prepared statements, for example, ADO - Command.Prepared = True. You can trap the error in code when you attempt to create the temporary table as a prepared statement if you open a new connection (without closing the current connection) and execute the statement. Note that another connection is opened and the connection that caused the temporary object conflict is out of scope for this connection, and the statement should execute normally. Otherwise, you can wait for the default connection timeout of 60 seconds and try again.

Experiment with Connection Pooling with the sample application to gain a better understanding of how Connection Pooling affects the Tempdb environment and how you can avoid overlapping results.

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:

Q151536 INF: SQLPrepare and Temporary Stored Procedures in SQL Server

Q155818 INF: Cannot SQLPrepare() the Creation of Temporary Objects

For additional information about advanced features of ActiveX Data Objects (ADO), please see the following article in the Microsoft Knowledge Base:

Q169470 INF: Frequently Asked Questions About ODBC Connection Pooling

Information is also available by querying for the phrase "Connection Pooling" in Microsoft Developer Network (MSDN).

Additional query words: prodsql

Keywords : kbfile kbsample kbSQL kbVBp500
Version : WINDOWS:5.0; winnt:6.5
Platform : WINDOWS winnt
Issue type : kbinfo