PRB: Access Violation Occurs When You Execute a Parameterized Query with SQLOLEDB (829402)


The information in this article 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.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. 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("adodb.connection")
' 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 "provider=sqloledb;data source=.;initial catalog=NorthWind;integrated security=sspi;"
Set cmd = CreateObject("adodb.command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "Select C.CustomerID, (select count(*) from orders O where O.CustomerID= C.CustomerID) as Qty from customers C where C.CustomerID >= ?"
cmd.Prepared = True
' Uncomment the following 2 lines to set the parameter information 
' and to avoid the access violation.
'cmd.parameters.append cmd.createparameter("p1",adVarChar,adParamInput,5)
'cmd.parameters(0).value = "BLONP"
Set rs = cmd.Execute
count = 0
While Not rs.EOF
  count = count + 1
  rs.MoveNext
Wend
Wscript.Echo "Fetched " & count & " records"
' END VBSCRIPT SAMPLE
  2. Save the file as c:\test.vbs.
  3. 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(?,''), ?)
Modification Type:MajorLast Reviewed:12/11/2003
Keywords:kbprb KB829402 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.