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

This article was previously published under Q283837
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 100928 (SQLBUG_70)
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.
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 theMicrosoft 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.

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.
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 querySELECT Col1004,ta."au_id" Col1007 FROM    (      SELECT t."price" Col1005,t."title_id" Col1002,t."type" Col1004,t."title" Col1003       FROM "pubs"."dbo"."titles" t WHERE t."price">(100.0000)   ) Qry1006,   "pubs"."dbo"."titleauthor" ta WHERE ta."title_id"=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.
derived table incorrect syntax error 7399

Article ID: 283837 - Last Review: 10/23/2013 15:46:01 - Revision: 1.0

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB283837