Article ID: 305387
Article Last Modified on 6/28/2004
APPLIES TO
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.1 Service Pack 1
- 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
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.1 Service Pack 1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.5 Service Pack 1
- Microsoft Data Access Components 2.5 Service Pack 2
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.6 Service Pack 1
- Microsoft Data Access Components 2.7
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 7.0 Service Pack 1
- Microsoft SQL Server 7.0 Service Pack 2
- Microsoft SQL Server 7.0 Service Pack 3
- Microsoft SQL Server 2000 Service Pack 1
This article was previously published under Q305387
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.
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.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION
Steps to Reproduce Behavior
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))- Open a new Standard EXE project in Visual Basic. Form1 is created by default.
- Add a project reference to the Microsoft ActiveX Data Objects 2.x Library.
- Place a command button on Form1.
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- 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.
- 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.
- Run the Visual Basic project.
- 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.
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.
Additional query words: execute
Keywords: kbbug kbpending KB305387
