BUG: UpdateBatch Generates SQL Statement That Cannot Reuse SQL Server Query Plans

The OLE DB Provider for SQL Server generates sp_executesql statements when it runs the UpdateBatch method of an ADO Recordset object to run batch updates. However, the generated sp_executesql statements may not be able to reuse SQL Server query plans when you use the ADO Recordset to insert or modify records with varchar or nvarchar columns.

This problem occurs because the parameterized queries (which are generated to run the data modification operations) use the number of characters in the corresponding field values instead of the column size (which the underlying metadata defines) to specify the size of the varchar and nvarchar fields.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

1. Run the following T-SQL statement in one of your SQL Server databases to create a sample table named Emp_test:

   Create table Emp_test (
   empid int primary key,
   empname varchar(20))
   					

2. Open a new Standard EXE project in Visual Basic. Form1 is created by default.
3. Add a project reference to the Microsoft ActiveX Data Objects 2.x Library.
4. Place a command button on Form1.
5. Copy and paste the following code into the Click event procedure of the command button.
   
   Note You must change User ID =<UID> to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.

   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   
   Set cn = New ADODB.Connection 
   Set rs = New ADODB.Recordset 
   
   cn.Open "Provider=SQLOLEDB;Data Source=SQL Server;Initial Catalog=pubs;User id=<uid>"
   rs.CursorLocation = adUseClient
   rs.Open "Select * from emp_test", cn, adOpenStatic, adLockBatchOptimistic
   Set rs.ActiveConnection = Nothing
   
   rs.AddNew
   rs.Fields(0) = 1
   rs.Fields(1) = "aaa"
   rs.Update
   Set rs.ActiveConnection = cn
   rs.UpdateBatch
   
   Set rs.ActiveConnection = Nothing
   rs.AddNew
   rs.Fields(0) = 2
   rs.Fields(1) = "bbbb"
   rs.Update
   Set rs.ActiveConnection = cn
   rs.UpdateBatch
   
   rs.Close
   cn.Close
   					

6. Modify the ADO Connection string in the cn.Open statement to point to the SQL Server database in which you created the Emp_test sample table.
7. In SQL Server Profiler, start a new trace against the server on which you created the Emp_test sample table to view the T-SQL statements that are run against it.
8. Run the Visual Basic project.
9. Click the Command button on Form1 to run the ADO code that inserts two records into the Emp_test sample table by running the UpdateBatch method of a client-side, static, lock batch optimistic ADO Recordset.
10. Switch to the SQL Server Profiler window, and view the sp_executesql T-SQL statements that are generated to insert the two records into the Emp_test table. Notice the following sp_executesql statements:

    exec sp_executesql N'INSERT INTO "pubs".."emp_test" ("empid","empname") 
    VALUES (@P1,@P2)', N'@P1 int,@P2 varchar(3)', 1, 'aaa'
    
    exec sp_executesql N'INSERT INTO "pubs".."emp_test" ("empid","empname") 
    VALUES (@P1,@P2)', N'@P1 int,@P2 varchar(4)', 2, 'bbbb'
    					

The size of the parameters that are used to insert values into the empname varchar column are defined based on the number of characters in the value that is being inserted. This prevents the second sp_executesql statement from reusing the query plan that SQL Server generates for the first sp_executesql statement because the sizes of the @P2 parameter in the two statements differ.