Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 297035 - Last Review: September 26, 2005 - Revision: 2.3
FIX: Correlation Error Using Parameters.Refresh with SQLOLEDB
This article was previously published under Q297035
When executing a parameterized UNION query with the SQLOLEDB OLEDB Provider, the following error may be reported:
Run-time error '-2147467259 (80004005)':
The correlation name 'O' is specified multiple times in a FROM clause.
To resolve this problem, obtain the latest service pack for SQL Server 2000, the latest service pack for MDAC 2.6, or the hotfix referenced below:
For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
290211Â
(http://kbalertz.com/Feedback.aspx?kbNumber=290211/EN-US/
)
INF: How to Obtain the Latest SQL Server 2000 Service Pack
For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
300635Â
(http://kbalertz.com/Feedback.aspx?kbNumber=300635/EN-US/
)
INFO: How to Obtain the Latest MDAC 2.6 Service Pack
Hotfix
The English version of this fix should have the following file attributes or later:
Date Version Size File name Platform
-------------------------------------------------------------
05/17/2001 2000.080.0294 491,584 Sqloledb.dll x86
05/17/2001 2000.080.0294 61,440 Sqloledb.rll x86
To work around this issue, make sure that the order of the table names in each of the SELECT statements is the same in each UNION query.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2.
To reproduce this problem, run the following Microsoft Visual Basic code:
Sub CorrelationReproCode()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sql As String
Dim fUseOutOfOrderUNION As Boolean
Set conn = New ADODB.Connection
conn.Open "Provider=sqloledb;Server=.;Database=northwind;" & _
"Integrated Security=SSPI;"
' Change this to False to avoid the problem.
fUseOutOfOrderUNION = True
If (fUseOutOfOrderUNION) Then
sql = "SELECT O.OrderDate FROM Customers R, Orders O " & _
"WHERE R.CustomerId = O.CustomerId AND R.CustomerId = ? " & _
"Union " & _
"SELECT O.OrderDate FROM Orders O, Customers R " & _
"WHERE R.CustomerID = O.CustomerID AND R.Region = ?"
Else
sql = "SELECT O.OrderDate FROM Customers R, Orders O " & _
"WHERE R.CustomerId = O.CustomerId AND R.CustomerId = ? " & _
"Union " & _
"SELECT O.OrderDate FROM Customers R, Orders O " & _
"WHERE R.CustomerID = O.CustomerID AND R.Region = ?"
End If
Set cmd = New ADODB.Command
cmd.CommandText = sql
Set cmd.ActiveConnection = conn
' Error will occur here if fUseOutOfOrderUNION is set to True.
cmd.Parameters.Refresh
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
End Sub
APPLIES TO
- Microsoft OLE DB Provider for SQL Server 7.0
- Microsoft OLE DB Provider for SQL Server 7.01
- Microsoft Data Access Components 2.6
| kbhotfixserver kbqfe kbbug kbfix kbmdac260sp2fix kbsqlserv2000presp2fix KB297035 |
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
|
Aji
- ajichennakkadan NOSPAM-AT-NOSPAM rediffmail.com
|
Report As Irrelevant
|
| Written:
7/27/2004 8:36 AM |
|
|