Microsoft KB Archive/278984

= PRB: Error 7321 Occurs When You Run a Query That Uses Four-Part Names Against DB2 on an AS/400 Computer =

Article ID: 278984

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q278984



SYMPTOMS
When all of the following conditions are met:
 * You have defined a linked server with DB2 on AS/400 as a back-end server.
 * You run a SQL Server query using four-part names against the linked server.
 * You enable the Nested queries property of the OLE DB provider that is used by the linked server.

The SQL Server statement may return the following error message:

Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while

preparing a query for execution against OLE DB provider 'DB2OLEDB'.

[OLE/DB provider returned message: A SQL error has occurred. Please

consult the documentation for your specific DB2 version for a

description of the associated Native Error and SQL State.

SQLSTATE: 42707, SQLCODE: -208]



CAUSE
This behavior is by design.

The optimizer in SQL Server tries to maximize the amount of computation that is pushed to the linked server for queries that use four-part names. This may include re-phrasing the query to maximize the remote computations. When the Nested queries property of the OLE DB provider is enabled, SQL Server may even send ORDER BY clauses to the linked server. The root cause of this problem is that SQL Server uses an ORDER BY syntax that is compatible with most SQL dialects, but not with one of the versions of DB2 for AS/400.



WORKAROUND
Here are two possible workarounds:
 * Use the OPENQUERY syntax instead of the four-part names. For more information, refer to SQL Server Books Online.
 * Disable the Nested queries option of the OLE DB provider.



Background
SQL Server 7.0 and SQL Server 2000 are compliant to the Entry level of the SQL-92 standard. This standard specifies an ORDER BY clause only for a DECLARE CURSOR statement, not for a plain SELECT statement. However, the SQL-92 standard does specify that you need to use the column name and not the column alias in the GROUP BY, WHERE and HAVING clauses for plain SELECT statements. So, SQL Server uses a syntax that is in line with the explicit rules of the SQL-92 standard and is supported by most other OLE DB providers and SQL dialects. Unfortunately, there are versions of DB2 on AS/400 that do not understand this syntax. If you think you are experiencing this problem, you can confirm this by using these steps:  Run set showplan_text on before you run the problem query to get the query plan.  Look at all the nodes of the query plan that are marked &quot;Remote Query&quot;. If there is one remote query that looks like this: &quot;SELECT ColName ColAlias FROM  ...ORDER BY ColName&quot; You are experiencing this problem.  Run set showplan_text off to disable the output of query plans.

Steps to Reproduce Problem
Given there are copies of the tables authors, titles and titleauthor of the pubs database on the DB2 system and the preceding requirements are met, you can reproduce the problem with this SQL statement (assuming that the linked server=&quot;DB2&quot;, catalog=&quot;Salvator&quot; and schema=&quot;pubs&quot;): select a.AU_ID, a.AU_LNAME, a.AU_FNAME, a.PHONE, a.CITY, t.TITLE from DB2.SALVATOR.PUBS.AUTHORS a left join DB2.SALVATOR.PUBS.TITLEAUTHOR ta on (a.AU_ID=ta.AU_ID and ta.AU_ORD>1) left join DB2.SALVATOR.PUBS.TITLES t on ta.TITLE_ID=t.TITLE_ID order by a.AU_LNAME, a.AU_FNAME The query produces this plan:  |--Nested Loops(Left Outer Join) |--Remote Query(SELECT a.AU_ID Col1008,a.AU_LNAME Col1009,a.AU_FNAME Col1010,a.PHONE Col1011,a.CITY Col1012 FROM SALVATOR.PUBS.AUTHORS a ORDER BY a.AU_LNAME ASC,a.AU_FNAME ASC) |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[TITLE_ID])=([DB2].[SALVATOR].[PUBS].[TITLES].[TITLE_ID]), RESIDUAL:([DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[TITLE_ID]=[DB2].[SALVATOR].[PUBS].[TITLES].[TITLE_ID])) |--Filter(WHERE:([DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[AU_ID]=[DB2].[SALVATOR].[PUBS].[AUTHORS].[AU_ID] AND [DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[AU_ORD]>1)) |   |--Table Spool |        |--Remote Query(SELECT ta.TITLE_ID Col1024,ta.AU_ID Col1023,ta.AU_ORD Col1025 FROM SALVATOR.PUBS.TITLEAUTHOR ta ORDER BY ta.TITLE_ID ASC) |--Table Spool |--Remote Query(SELECT t.TITLE Col1022,t.TITLE_ID Col1021 FROM SALVATOR.PUBS.TITLES t ORDER BY t.TITLE_ID ASC) NOTE: The last &quot;Remote Query&quot; node sends this SQL statement to the linked server: SELECT ... t.TITLE_ID Col1021 FROM ... ORDER BY t.TITLE_ID ASC The preceding line is the offending part of the query.

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Additional query words: OLEDB DB2 AS/400 Error 7321

Keywords: kbprb KB278984

-

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

© Microsoft Corporation. All rights reserved.