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

Article translations Article translations
Article ID: 283837 - View products that this article applies to.
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)
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.

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

Properties

Article ID: 283837 - Last Review: October 23, 2013 - Revision: 1.0
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB283837

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com