SQL Server Performance Forum – Threads Archive
Full Text Returns Nothing
In SQL 2000In a Db that already has a working indexed table…
In the MMC
1) I create a table named test with 2 fields ID (numeric PrimaryKey) and Memo (Text)
2) I populate the table with 5 rows having Memo = ‘car’ or ‘house’ or ‘pencil’
3) Right click on it and choose: ‘Full_text Index Table’-> ‘Define Full text…’
4) Select all defaults ( Memo is the field to index )
5) NO schedule
6) I go to the full-text Catalogs and right click on the created catalog and ‘start the full population’ The catalog properties shows:
Item count = 0
Catalog size = 1MB
Unique key count = 1 When I query the table this is what I get:
select count(*) from test where memo = ‘car’ result = 1
select count(*) from test where memo like ‘car%’ result = 4
select count(*) from test where CONTAINS(memo, ‘car’) result = 0
select count(*) from test where Freetext(memo, ‘car’) result = 0 I tried rebuilding the indexes restarting the full-text engine, the server etc. Any ideas? This is killing me, I spent most of the day on it with no luck. Any help would be greatly appreciated.
Thanks
Try to restart MSSearch service and how about SQL & OS version used alongwith service pack level. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
SQL Server version:
Microsoft SQL Server 2000 – 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) Running on Win 2K server.
In the mean time I tried to set the full text on another 2 servers (with identical SQl/Os vversions) with the same result. This makes me think I’m missing a step or something even though I set up 2 other servers 2 weeks ago (that work) and I don’t remember doing anything different.
8.00.194 is no service pack and try to upgrade to SP3a (test it before). _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
That’s strange since it says SP3 is installed at the end of the @@vesion message. But you are right the version has not upgraded.
I’ll put sp3a on the spare server. Thx
Windows NT 5.0 (Build 2195: Service Pack 3) – its for Windows 2K not for SQL server and that is represented by the number 8.00.194 and I’m sure 8.00.760 refers SP3/3a. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
After a closer inspection I here is the mess.
I installed SQL 2000 and originally thought installed succesfully SP3 on 4 servers but here is what I get: Server1 Live:
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) Server2 Live:
Microsoft SQL Server 2000 – 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) Server3 Backup:
Microsoft SQL Server 2000 – 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Server4 Backup:
Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) So I’ll apply SP3a on all but the live server 1 that seems ok. Hopefully this will fix the full-text issue too.
Yes server 1 and 4 are seems to patched upto Sp3a so no need to reapply and apply on 2 & 3 or on the server where you’d this issue. And make sure to check MSSearch service is up and running. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
FYI it just occured to me that the SP is referring to the OS…
Boy this is going to be a long day…
Ok All servers are now 8.00.760. Server live still has the issue server backup is ok. I rebooted them with no luck. I went through the steps once more w NO luck. In MMC I restarted the Full-text search Support Service with no luck. The service is definitely running because on another Db on the same server the full text search works flawlessly. I also dropped the Db with issues and recreated it + went through the process w No luck. Anything else anyone can think of?
I unistalled sql server on the backup server and then reinstalled it and even without the SP3a the full-text search works flawlessly.
I’ll try again when the SP3a is installed. This is obviously the extreme solution to the issue and I would need to rebuild/restore all DBs (auch) any better ideas?
I suspect there could be some process or mismatch of .DLL that could stop the FTS to work properly and its glad you’re back on track and as planned try with Sp3a also. But before that I suggest to contact MS support for any fix or information as going from scratch is a large work. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
The issue was related to the removal of the builtinadministrator account (to exclude domain admins from the sysadmin role).
It was not apparent at first because it manifested itself only months after the change was made (when the new Db was setup).
The old indexes were ok because the account was only needed at population time. Now the issue is that domain admins still have sysadmin role, but that’s another story…
You can replace the account builtinadministrator with nt authoritysystem and your FTS will still work Cheers
Twan
Thanks Twan I did that last night as discribed on: http://www.microsoft.com/technet/tr…nol/sql/maintain/operate/opsguide/sqlops3.asp And it works beautifully, now I’m hoping I don’t run in any more trouble for a while, I need some peace… Cheers mate
Stefano
]]>