Microsoft KB Archive/283837

= FIX: SQL Server May Generate Nested Query For Linked Server When Option Is Disabled =

Article ID: 283837

Article Last Modified on 10/15/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q283837



BUG #: 100928 (SQLBUG_70)



SYMPTOMS
SQL Server may generate a nested query to a linked server even if the option is disabled in the linked server's options, accessible in SQL Enterprise Manager under the Options tab of the Linked Server Properties dialog box.

If the remote server does not support nested queries, this may result in an error that prevents the query from running. The exact error depends on the provider that is being used; however, SQL Server will most likely report a 7399 error in such cases.

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'DataDirect.InformixADOProvider' reported an error. The provider did not give any information about the error.

If you experience this problem, you should be able to view the SHOWPLAN_ALL or SHOWPLAN_TEXT output for the generated plan. However, attempts to run the query fail.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack

NOTE: The following hotfix was created prior to Microsoft SQL Server 7.0 Service Pack 4.

The English version of this fix should have the following file attributes or later:   Version      File name      Platform ---

7.00.0921   s70921i.exe    x86       SP2-based fix 7.00.0921   s70921a.exe    Alpha     SP2-based fix

7.00.0970   s70970i.exe    x86       SP3-based fix 7.00.0970   s70970a.exe    Alpha     SP3-based fix NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.



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 Microsoft SQL Server 7.0 Service Pack 4.



MORE INFORMATION
A nested query is one in which a SELECT statement is nested in the FROM clause of your main query. This is also known as a derived table. An example of a nested query from the pubs database might look like similar to: --Original query: select t.type, ta.au_id from REMOTE_SRV.pubs.dbo.titles t, REMOTE_SRV.pubs.dbo.titleauthor ta where t.title_id = ta.title_id and price > 100.0

--Generated query plan containing nested query SELECT Col1004,ta.&quot;au_id&quot; Col1007 FROM (     SELECT t.&quot;price&quot; Col1005,t.&quot;title_id&quot; Col1002,t.&quot;type&quot; Col1004,t.&quot;title&quot; Col1003       FROM &quot;pubs&quot;.&quot;dbo&quot;.&quot;titles&quot; t WHERE t.&quot;price&quot;>(100.0000)   ) Qry1006, &quot;pubs&quot;.&quot;dbo&quot;.&quot;titleauthor&quot; ta WHERE ta.&quot;title_id&quot;=Col1002) Certain remote data sources may not support this nested query syntax and can not run this query, which SQL Server attempts to run. To determine if you may be encountering this problem, view the SHOWPLAN_ALL or SHOWPLAN_TEXT output and see if SQL Server has generated a query of this form.

Additional query words: derived table incorrect syntax error 7399

Keywords: kbbug kbfix KB283837

-

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

© Microsoft Corporation. All rights reserved.