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

View products that this article applies to.

This article was previously published under Q283837

BUG #: 100928 (SQLBUG_70)

Expand all | Collapse all

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.

Back to the top

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 (http://support.microsoft.com/kb/301511/EN-US/ ) 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.

Back to the top

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.

Back to the top

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)

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.

Back to the top