Microsoft KB Archive/829402

= PRB: Access Violation Occurs When You Execute a Parameterized Query with SQLOLEDB =

Article ID: 829402

Article Last Modified on 12/11/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for SQL Server

-





SYMPTOMS
When you use the Microsoft OLE DB Provider for SQL Server, and you call the ICommand::Prepare method on a parameterized SQL statement that contains a subquery or function, but you do not call the ICommandWithParameters::SetParameterInfo method for the parameters before you do this, an access violation may occur.



CAUSE
In Microsoft OLE DB Provider for SQL Server, you must call the ICommandWithParameters::SetParameterInfo method to describe parameter information when any SQL statement contains a subquery or function. You must do this even if the parameters are outside the subquery or function.



RESOLUTION
If you execute a SQL command that contains a subquery or function with the Microsoft OLE DB Provider for SQL Server, always describe the parameters by using the ICommandWithParameters::SetParameterInfo method before you call the ICommand::Prepare method.



STATUS
This behavior is by design.



Steps to Reproduce the Behavior
  Paste the following vbscript code sample in Notepad: ' START VBSCRIPT SAMPLE Dim conn, cmd, rs, count

Const adParamInput = 1 Const adVarChar = 200

Set conn = CreateObject(&quot;adodb.connection&quot;) ' Note: This sample assumes you have a local SQL Server server with the SQL NorthWind sample database. ' Change the connection string as appropriate for you environment. conn.Open &quot;provider=sqloledb;data source=.;initial catalog=NorthWind;integrated security=sspi;&quot; Set cmd = CreateObject(&quot;adodb.command&quot;) Set cmd.ActiveConnection = conn cmd.CommandText = &quot;Select C.CustomerID, (select count(*) from orders O where O.CustomerID= C.CustomerID) as Qty from customers C where C.CustomerID >= ?&quot; cmd.Prepared = True ' Uncomment the following 2 lines to set the parameter information ' and to avoid the access violation. 'cmd.parameters.append cmd.createparameter(&quot;p1&quot;,adVarChar,adParamInput,5) 'cmd.parameters(0).value = &quot;BLONP&quot; Set rs = cmd.Execute count = 0 While Not rs.EOF count = count + 1 rs.MoveNext Wend Wscript.Echo &quot;Fetched &quot; & count & &quot; records&quot; ' END VBSCRIPT SAMPLE  Save the file as c:\test.vbs.  To run this code at a command prompt, use the following command line: cscript c:\test.vbs 

Note These steps demonstrate how to reproduce this error with a statement that contains a subquery. A statement that contains a function can also cause this error. The following is an example: INSERT INTO TestTable(col1, col2, col3) VALUES (?, ISNULL(?,''), ?)

Keywords: kbprb KB829402

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.