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:
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
Keywords: kbbug kbfix kbqfe kbsqlserv2000presp2fix kbmdac260sp2fix kbhotfixserver KB297035
