Microsoft KB Archive/193095

= PRB: Transactions and ADO Parameters Cause Connections to Hang =

Article ID: 193095

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q193095



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
  Create a Stored Procedure that has return and input parameters. For example: create procedure proctest(@in text) as return 1  Create a new Standard EXE Visual Basic project. Set a reference to the ActiveX Data Objects Library 2.x. Place two command buttons on Form1, the default form.  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 </li> 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.</li> 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.</li></ol>

<div class="references_section">