PRB: Transactions and ADO Parameters Cause Connections to Hang

If you try to populate an ActiveX Data Objects (ADO) command object's parameters collection, automatically from within a transaction, other connections to the same SQL Server hang if they attempt to modify information in the system tables of the TempDB database.

This behavior only occurs if you use the default ODBC provider for ADO in conjunction with SQL Server version 6.5.

When an application attempts to populate the parameters collection of an ADO command object automatically, it queries the system tables of the SQL Server database for parameter-specific information (such as the total number of parameters, the type of each parameter, the direction of each parameter, and so forth). This information is returned to the calling application through a temporary table created in the TempDB database of SQL Server. The creation of a temporary table adds a record to the system tables of the TempDB database.

If this is done from within a transaction, the addition of a record to the system tables of the TempDB database results in the system tables of the TempDB database being locked until the transaction has been either committed or rolled back.

If a process on another connection to the SQL Server attempts to modify the information in the system tables of the TempDB database, while the database is locked by a preceding process, the process hangs until the transaction is completed.

To avoid this behavior, you can implement either of the following two programming concepts:

• You can manually populate the parameters collection (by explicitly creating the parameter in your code and then appending that parameter to the parameters collection of the command object). Because no trip to the server is needed to define the parameters in the parameters collection when manual population is used, no temporary tables need to be created in the TempDB database (TempDB database is never locked by the transaction).
• You can also continue to use the automatic population method, but you should populate the parameters collection outside the scope of the transaction.

This behavior is by design.

The following Visual Basic sample code demonstrates the hanging behavior described in the SYMPTOMS section. This behavior can also be seen in other ADO-compliant programming languages such as Java and Visual C++.

Note that the following sample application hangs when executed and needs to be terminated through the Task Manager window.

Steps to Reproduce Behavior

1. Create a Stored Procedure that has return and input parameters. For example:

   create procedure proctest(@in text) as return 1

2. Create a new Standard EXE Visual Basic project.
3. Set a reference to the ActiveX Data Objects Library 2.x.
4. Place two command buttons on Form1, the default form.
5. Paste the following code into the code for Form1:

   Option Explicit
   
    Dim Con1 As New ADODB.Connection
    Dim Con2 As New ADODB.Connection
    Dim Cmd1 As New ADODB.Command
    Dim Cmd2 As New ADODB.Command
    Dim ServerName As String
    Dim UserID As String
    Dim Password As String
    Dim ConString As String
   
    Private Sub Command1_Click()
   
    On Error Resume Next
   
    ' Restore error handling.
    On Error GoTo 0
   
    ' Specify properties of the command object.
    Set Cmd1.ActiveConnection = Con1
    Cmd1.CommandType = adCmdStoredProc
    Cmd1.CommandText = "proctest"
   
    ' Begin Transaction
    Con1.BeginTrans
    Cmd1.Parameters.Refresh
   
    End Sub
   
    Private Sub Command2_Click()
   
    On Error Resume Next
   
    ' Specify properties of the command object.
    Set Cmd2.ActiveConnection = Con2
    Cmd2.CommandType = adCmdStoredProc
    Cmd2.CommandText = "{? = call proctest(?)}"
   
    ' Begin Transaction.
    Con2.BeginTrans
    Cmd2.Parameters.Refresh
   
    End Sub
   
    Private Sub Form_Load()
   
    ServerName = "Govind3"
    UserID = "sa"
    Password = ""
   
    ConString = "Driver={SQL Server};Server=" & ServerName
    ConString = ConString  & ";Database=Pubs;"
    ConString = ConString  & "UID="  & UserID
    ConString = ConString  & ";PWD=" & Password  & ";DSN='';"
   
    Con1.Open ConString
    Con2.Open ConString
   
    End Sub
   
    Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As
    Integer)
   
    Con1.Close
    Con2.Close
    Set Cmd1 = Nothing
    Set Cmd2 = Nothing
    Set Con1 = Nothing
    Set Con2 = Nothing
   
    End Sub
   

6. Modify the values assigned to the ServerName, UserID, and Password variables in the Form_Load event to suit the connection needs of your SQL Server 6.5 environment.
7. Run the application. Click the Command1 button. Then click the Command2 button. The application hangs indefinitely until it is terminated through the Task Manager window.

For additional information about Refreshing ADO Parameters for stored procedures, please see the following article in the Microsoft Knowledge Base: