Microsoft KB Archive/297035

= FIX: Correlation Error Using Parameters.Refresh with SQLOLEDB =

Article ID: 297035

Article Last Modified on 9/26/2005

-

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

-



This article was previously published under Q297035



SYMPTOMS
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.



RESOLUTION
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 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 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



WORKAROUND
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.



STATUS
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.



MORE INFORMATION
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 &quot;Provider=sqloledb;Server=.;Database=northwind;&quot; & _ &quot;Integrated Security=SSPI;&quot; ' Change this to False to avoid the problem. fUseOutOfOrderUNION = True If (fUseOutOfOrderUNION) Then sql = &quot;SELECT O.OrderDate FROM Customers R, Orders O &quot; & _ &quot;WHERE R.CustomerId = O.CustomerId AND R.CustomerId = ? &quot; & _ &quot;Union &quot; & _ &quot;SELECT O.OrderDate FROM Orders O, Customers R &quot; & _ &quot;WHERE R.CustomerID = O.CustomerID AND R.Region = ?&quot; Else sql = &quot;SELECT O.OrderDate FROM Customers R, Orders O &quot; & _ &quot;WHERE R.CustomerId = O.CustomerId AND R.CustomerId = ? &quot; & _ &quot;Union &quot; & _ &quot;SELECT O.OrderDate FROM Customers R, Orders O &quot; & _ &quot;WHERE R.CustomerID = O.CustomerID AND R.Region = ?&quot; 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

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp2fix kbmdac260sp2fix kbhotfixserver KB297035

-

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

© Microsoft Corporation. All rights reserved.