This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 364091 (SHILOH_BUGS)
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
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.
Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
If you review the execution plan for the query, you can see the remoting of the Sub-SELECT function in the "RemoteQuery" node of the execution plan. The following query demonstrates the incorrect remoting of the query:
declare @startDate datetimedeclare @endDate datetimeselect @startDate = '2003-01-15'select @startDate = '1998-06-03'select orderID, customerID, employeeID into tTempfrom [remoteServer].northwind.dbo.orderswhere customerID in ('NORTS', 'BLAUS', 'GREAL') and requiredDate >= @startDate and requiredDate <= @endDateorder 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."OrderID" Col1015,Tbl1001."CustomerID" Col1016,Tbl1001."EmployeeID" Col1017