The incorrect data is retrieved when you use the Command.Execute method to run an SQL batch file (826457)

SYMPTOMS

In a Microsoft Visual Basic .NET application that uses Microsoft Component Object Model (COM) interoperability to access the members of the Microsoft ActiveX Data Objects Library, when you call the parameterless Command.Execute method to run an SQL batch file that retrieves data from a global temporary table, you may notice that this method retrieves incorrect or incomplete data.

Note You do not notice this behavior if you call the Command.Execute method to run an SQL batch file that retrieves data from a local temporary table.

CAUSE

You notice the behavior that is mentioned in the "Symptoms" section of this article when your SQL batch file contains code to retrieve data that depends on successfully completing an earlier call to the parameterless Command.Execute method. Additionally, this earlier call to the parameterless Command.Execute method runs another SQL batch file.

When you make the initial call to the parameterless Command.Execute method, the corresponding SQL batch file uses a Connection object to run. If you do not explicitly associate a Connection object with your Command object, the common language runtime implicitly creates a Connection object for your SQL batch file to use. Additionally, the common language runtime creates a Recordset object that remains in memory until the garbage collector releases this object. This Recordset object blocks the additional running of code that uses the current Connection object until the garbage collector performs garbage collection.

Because the current Connection object is blocked, when you make your next call to the Command.Execute method, the common language runtime creates another Connection object to run the next SQL batch file. Because the two SQL batch files use different Connection objects, a race condition may occur. If a race condition occurs, the code that is contained in this SQL batch file retrieves data from the global temporary table before the code that is contained in the initial SQL batch file manipulates the corresponding dependent data. Therefore, you notice the behavior that is mentioned in the "Symptoms" section of this article.

WORKAROUND

To work around this problem, pass the ExecuteOptionEnum.adExecuteNoRecords value to the Options parameter when call the Command.Execute method. When you pass this value, the common language runtime does not create a Recordset object that blocks your Connection object. Therefore, your next call to the Command.Execute method can use the same Connection object and you will not notice any race conditions. To do this, use code that is similar to the following sample code:

' Pass the ExecuteOptionEnum.adExecuteNoRecords value to the Command.Execute method.
cmd.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)

Additionally, Microsoft recommends that you always implement the following coding best practices in your code:

Create and open an explicit Connection object, and then set the MultipleConnections property of this object to False. After you do this, you will receive an error message if the common language runtime tries to use a different Connection object to run your SQL batch files. You can use this error message to prevent the common language runtime from using a different Connection object. To do this, use code that is similar to the following sample code:
```
' Declare a new Connection object.
Dim cn As ADODB.Connection
' Create and then open the Connection object.
cn = New ADODB.Connection
cn.Open(connString)
' Set the MultipleConnections property to False.
cn.Properties("MultipleConnections").Value = False
```
Note Replace connString with a connection string that you can use.
Explicitly associate your Connection object with the Command object so that the common language runtime does not implicitly create another Connection object. To do this, use code that is similar to the following sample code:
```
' Explicitly associate your Connection object with the Command object.
cmd.ActiveConnection = cn
```
Explicitly close the Recordset object and the Connection object (in that order) when you finish using these objects so that the garbage collector can release these objects. To do this, use code that is similar to the following sample code:
```
' Explicitly close the Recordset object and the Connection object.
rs.Close()
cn.Close()
```

MORE INFORMATION

Steps to reproduce the behavior

Start Microsoft Visual Studio .NET.
Use Visual Basic .NET to create a Windows Application project. By default, the Form1.vb file is created.
In your Visual Basic .NET project, add a project reference to the Microsoft ActiveX Data Objects Library.
Add the following code at the top of the Form1.vb file:
```
Imports ADODB
```

In your Form1.vb file, use code that is similar to the following sample code to call the parameterless Command.Execute method to run two SQL batch files:

' Use the strBatch1 variable to specify the first SQL batch file to run.
cmd.CommandText = strBatch1
' Run the first SQL batch file.
cmd.Execute()
' Use the strBatch2 variable to specify the second SQL batch file to run.
cmd.CommandText = strBatch2
' Run the second SQL batch file.
cmd.Execute()

Build and then run your application. You may notice the behavior that is mentioned in the "Symptoms" section of this article.