Article ID: 888008
Article Last Modified on 11/2/2007
APPLIES TO
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Personal Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Workgroup Edition
- Microsoft SQL Server 2000 Desktop Engine (Windows)
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition 64-bit
Bug #: 471528 (SQL Server 8.0)
SYMPTOMS
When you run a full-text query that uses the NEAR operator in Microsoft SQL Server 2000, the number of rows that are returned may be different if the NEAR operands are reversed. For example, the following two queries may return different results:
- SELECT * FROM IndexedTable WHERE CONTAINS (*, N'"abc" near "xyz"')
- SELECT * FROM IndexedTable WHERE CONTAINS (*, N'"xyz" near "abc"')
RESOLUTION
Service pack information
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more 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
Prerequisites
This hotfix requires SQL Server 2000 Service Pack 3.
Restart requirement
You do not have to restart your computer after you apply this hotfix.
Hotfix replacement information
This hotfix does not replace any other hotfixes.
File 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 -------------------------------------------------------------- 07-Aug-2004 00:28 9.107.8320.7 1,536,000 Tquery.dll
STATUS
Microsoft has confirmed that this is a bug 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
Steps to reproduce the behavior
To reproduce the behavior, run the following Transact-SQL script:
use master
go
If exists (Select dbid from master.dbo.sysdatabases where Name='DatabaseFT')
Drop Database DatabaseFT
go
Create Database DatabaseFT
go
set nocount on
go
use DatabaseFT
go
CREATE TABLE [IndexedTableTwo] (
[ID] [int] NOT NULL constraint pk_column11 primary key,
Column2 nvarchar(200) )
go
Insert Into IndexedTableTwo(ID , Column2) Values ( 1, N'CHAMPION ACQUISITION CORP ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 2, N'CHAMPION BUILDING PRODUCTS ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 3, N'CHAMPION CHEVROLET ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 4, N'CHAMPION DODGE INC ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 5, N'CHAMPION INTERNATIONAL CORP ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 6, N'CHAMPIONSHIP RACING TECHNOLOGY CRT ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 7, N'CHARLES CHAMPION')
Insert Into IndexedTableTwo(ID , Column2) Values ( 8, N'STUART CHAMPION')
Insert Into IndexedTableTwo(ID , Column2) Values ( 9, N'CURVE CHAMPION')
go
exec sp_fulltext_database 'enable'
go
exec sp_fulltext_catalog 'for_upgrade_FTC1', 'create'
go
exec sp_fulltext_table 'IndexedTableTwo', 'create', 'for_upgrade_FTC1', 'pk_column11'
go
exec sp_fulltext_column 'IndexedTableTwo', 'Column2', 'add'
go
exec sp_fulltext_table 'IndexedTableTwo', 'activate'
go
exec sp_fulltext_table 'IndexedTableTwo', 'start_full'
go
waitfor delay '000:00:02'
go
while ( ( select fulltextcatalogproperty ( 'for_upgrade_FTC1' , 'populatestatus') )<>0 )
begin
waitfor delay '000:00:02'
end
go
select ID, '|' + Column2 + '|' as SurroundedColumn2 from IndexedTableTwo
where contains (Column2, N'"CHAMPION*" near "C*"') order by ID
GO
select ID, '|' + Column2 + '|' as SurroundedColumn2 from IndexedTableTwo
where contains (Column2, N'"C*" near "CHAMPION*"') order by ID
GO
select ID, '|' + Column2 + '|' as SurroundedColumn2 from IndexedTableTwo
where contains (Column2, 'CORP near Champion')
Note After you run the Transact-SQL script, the first SELECT statement returns three rows, the second SELECT statement returns four rows, and the third SELECT statement returns two rows.
REFERENCES
For more information about full-text searches in SQL Server, visit the following Microsoft Developer Network (MSDN) Web site:
Additional query words: fulltext incorrect
Keywords: kbtshoot kbfix kbqfe kbsqlserv2000presp4fix kbprb kbquery KB888008
