Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 829402 - Last Review: December 11, 2003 - Revision: 2.0
PRB: Access Violation Occurs When You Execute a Parameterized Query with SQLOLEDB
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.
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.
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.
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("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
- Save the file as c:\test.vbs.
- To run this code at a command prompt, use the following command line:
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(?,''), ?)
APPLIES TO
- Microsoft OLE DB Provider for SQL Server
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate