Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q305387

↑ Back to the top


Symptoms

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.

↑ Back to the top


Cause

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.

↑ Back to the top


Status

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

↑ Back to the top


More information

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.

↑ Back to the top


Keywords: kbpending, kbbug, KB305387

↑ Back to the top

Article Info
Article ID : 305387
Revision : 5
Created on : 6/28/2004
Published on : 6/28/2004
Exists online : False
Views : 236