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

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.

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."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)