PRB: Transactions and ADO Parameters Cause Connections to Hang

ID: Q193095


The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1 SP2
  • Microsoft SQL Server version 6.5


SYMPTOMS

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.


CAUSE

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.


RESOLUTION

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.



STATUS

This behavior is by design.


MORE INFORMATION

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:


  2. create procedure proctest(@in text) as return 1 
  3. Create a new Standard.exe Visual Basic project. Place two command buttons on Form1, the default form.


  4. Paste the following code into the code for Form1:


  5. 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.



REFERENCES

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

Q174223 HOWTO: Refresh ADO Parameters for a Stored Procedure

Additional query words: mdacMisc kbADO kbSQLServ kbADO200 kbADO150

Keywords : kbADO150 kbADO200 kbSQLServ kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.0,1.5,2.0,2.1 SP2; winnt:6.5
Platform : WINDOWS winnt
Issue type : kbprb


Last Reviewed: November 11, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.