Microsoft KB Archive/815592

= FIX: Incorrect Remoting of a Predicate As a Sub-SELECT Function After SQL Server 2000 Service Pack 3 Upgrade =

Article ID: 815592

Article Last Modified on 9/27/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 3

-



BUG #: 364091 (SHILOH_BUGS)



SYMPTOMS
After you upgrade from SQL Server 2000 to SQL Server Service Pack 3 (SP3), a query that contains an ORDER BY clause and performs a SELECT INTO or INSERT SELECT query against a linked server may be incorrectly remoted as a Sub-SELECT function. If the linked server provider does not support Sub-SELECT functions, the provider may generate an error. The error you receive (that the provider generates) depends on the provider.



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

290211 How to obtain the latest SQL Server 2000 service pack

Hotfix information
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date         Time   Version            Size    File name --  26-Feb-2003  03:41  2000.80.777.0      29,244  Dbmslpcn.dll 08-Feb-2003 05:43                    786,432  Distmdl.ldf 08-Feb-2003 05:43                  2,359,296  Distmdl.mdf 30-Jan-2003 01:55                        180  Drop_repl_hotfix.sql 01-Feb-2003 00:32  2000.80.763.0   1,557,052  Dtsui.dll 30-Jan-2003 05:18                    746,470  Instdist.sql 30-Jan-2003 01:55                      1,402  Inst_repl_hotfix.sql 08-Feb-2003 06:40  2000.80.765.0      90,692  Msgprox.dll 08-Feb-2003 03:53                  1,065,895  Replmerg.sql 08-Feb-2003 06:40  2000.80.765.0     221,768  Replprov.dll 08-Feb-2003 06:40  2000.80.765.0     307,784  Replrec.dll 30-Jan-2003 05:18                  1,084,318  Replsys.sql 28-Feb-2003 01:34  2000.80.778.0     176,696  Sqlmap70.dll 08-Feb-2003 06:40  2000.80.765.0      57,920  Sqlrepss.dll 03-Mar-2003 19:15  2000.80.780.0   7,516,241  Sqlservr.exe 08-Feb-2003 06:40  2000.80.765.0      45,644  Sqlvdi.dll 26-Feb-2003 03:41  2000.80.777.0      29,244  Ssmslpcn.dll 26-Feb-2003 03:41  2000.80.777.0      82,492  Ssnetlib.dll 28-Feb-2003 01:34  2000.80.778.0      98,872  Xpweb70.dll

Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.



MORE INFORMATION
If you review the execution plan for the query, you can see the remoting of the Sub-SELECT function in the &quot;RemoteQuery&quot; node of the execution plan. The following query demonstrates the incorrect remoting of the query: declare @startDate  datetime declare @endDate   datetime

select @startDate = '2003-01-15' select @startDate = '1998-06-03'

select orderID, customerID, employeeID into   tTemp from   [remoteServer].northwind.dbo.orders where  customerID in ('NORTS', 'BLAUS', 'GREAL') and  requiredDate >= @startDate and  requiredDate <= @endDate order by customerID, requiredDate The following text is the truncated execution plan for the query: |--Table Insert(OBJECT:([tTemp])

|--Top(ROWCOUNT est 0)

|--Sort(ORDER BY:([ryanston-lptp].[northwind].[dbo].[orders].[CustomerID] ASC... |--Remote Query(SOURCE:(ryanston-lptp), QUERY:(SELECT Col1019,Col1017,Col1016,Col1015 FROM (SELECT Tbl1001.&quot;OrderID&quot; Col1015,Tbl1001.&quot;CustomerID&quot; Col1016,Tbl1001.&quot;EmployeeID&quot; Col1017

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp4fix kbhotfixserver KB815592

-

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

© Microsoft Corporation. All rights reserved.