FIX: Correlation Error Using Parameters.Refresh with SQLOLEDB (297035)


The information in this article 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 "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
Modification Type:MinorLast Reviewed:9/26/2005
Keywords:kbHotfixServer kbQFE kbbug kbfix kbmdac260sp2fix kbSQLServ2000preSP2Fix KB297035
©2004 Microsoft Corporation. All rights reserved.