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