System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
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"')
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
(http://support.microsoft.com/kb/290211/
)
How to obtain the latest SQL Server 2000 service pack
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
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.
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
Back to the top
