Microsoft KB Archive/815592: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - ">" to ">") |
||
Line 115: | Line 115: | ||
from [remoteServer].northwind.dbo.orders | from [remoteServer].northwind.dbo.orders | ||
where customerID in ('NORTS', 'BLAUS', 'GREAL') | where customerID in ('NORTS', 'BLAUS', 'GREAL') | ||
and requiredDate | and requiredDate >= @startDate | ||
and requiredDate <= @endDate | and requiredDate <= @endDate | ||
order by customerID, requiredDate</pre> | order by customerID, requiredDate</pre> |
Latest revision as of 10:21, 21 July 2020
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.
RESOLUTION
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 "Applies to" 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 "RemoteQuery" 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."OrderID" Col1015,Tbl1001."CustomerID" Col1016,Tbl1001."EmployeeID" Col1017
Keywords: kbbug kbfix kbqfe kbsqlserv2000presp4fix kbhotfixserver KB815592