PRB: Transactions and ADO Parameters Cause Connections to Hang

• 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:




create procedure proctest(@in text) as return 1 

2. Create a new Standard.exe Visual Basic project. Place two command buttons on Form1, the default form.



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

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



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