Fulltext - Full headache
-
I cannot get fulltext indexing to work, nor can I get any error messages to indicate whats wrong. Here's what I did (for the pubs database)
1. From the top menu, select Tools, Full Text Indexing.
2. Click next on the opening screen.
3. On the "Select a Database" screen, choose pubs.
4. For the next screen, select "titles" as the table and click next.
5. Keep the default unique index; there is only one on the pubs table.
6. In the next screen, select Title and Notes as the fields we would like indexed.
7. On the "Select a Catalog" screen, because this is the first Catalog on the server, we are prompted for a name. In this example, "demo" is used as the Catalog name.
8. Click next.
9. Click Finish.
10. From Enterprise Manager, expand Full-Text Catalogs
11. Right click and select "Start Full Population."
12. The catalog is now complete.
If I type sp_help_fulltext_catalogs everything looks ok.
If I type
SELECT title, notes FROM titles WHERE CONTAINS (notes, ' "quick easy" ')
I get 0 rows
Same if I enter
SELECT title, notes
FROM titles
--where notes like '%quick%'
WHERE CONTAINS (notes, ' quick ')
of course entering
SELECT title, notes
FROM titles
where notes like '%quick%'
gets me the record I want. Anyone have any ideas what I can check?
Thanks for the help.
Francis
-
I tried that and it works for me.
What does this return?
Select DATABASEPROPERTY('Pubs', 'IsFulltextEnabled')
-
I get a '1'. looks ok. At least if I got an error of sorts. My fulltext seach service is running under localaccount. Is this ok?
Francis
-
I run it under local too here. I don't know what else could be causing this.
-
I tried this on another server and it works fine. The server is does not work on uses Windows 2003 and SQL Server SP4. The server this works on is Windows 2000 Advanced server and SQL Server SP3A. I also tried it on another Windows 2003 server and it worked fine again... just not on my production box. AARRgggg!
Francis
-
I just don't know how to fix this one... I'll keep listening in case something comes up.
-
Francis,
This might be a dumb question, but are you sure the full-text population was completed before you ran your query?
Greg
Greg
-
Does the problem box have the BUILTIN\Administrators account as sysadmin? Check out the KB 317746 article.
Andy
-
We are definately onto something here. The box with problems does not have Builtin/Administrators as sysadmin anymore. (but then neither does the server that works) I ran the workaround described in KB317746;
EXEC sp_grantlogin [NT Authority\System]
EXEC sp_addsrvrolemember @loginame = [NT Authority\System]
, @rolename = 'sysadmin'
And it worked! Thanks to all for the help, especially Andy for pointing out the KB article.
Francis
-
Posting this after this issue had been resolved as others may read this in the future and gain from additional info...
First of all, troubleshooting SQL Server (7.0 or 2000) Full-text Search (FTS) or in this case Full-text Indexing (FTI) can be difficult, but it is possible. Secondly and most importantly for FTI issues, the first place to look is the server's Application event log for any "Microsoft Search" or MssCi source events as this the only place where such errors are written. In the App event log, under Microsoft Search (mssearch) you would of seen a failure to FT Index the table, this then would lead you to one or both of the following KB articles:
277549 "PRB: Unable to Build Full-Text Catalog After You Modify"
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component Services]
http://support.microsoft.com/default.aspx?scid=KB;EN-US;277549
317746 "PRB: SQL Server Full-Text Search Does Not Populate Catalogs"
http://support.microsoft.com/default.aspx?scid=kb;en-us;317746
Then depending upon the error, you can fix the problem!
Hope that helps,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane -
John,
Do you have a clue why the Scheduled Tasks application uses the SYSTEM account to login to SQL Server when it runs a task?
I found this after deleting the BUILTIN\Administrators SQL login and kept changing the task schedule and verifying that the login failure matched the Scheduled Tasks start time.
Index Server service was disabled at that time, so I think that I proved that Scheduled Tasks was the culprit. Especially when I changed the task from NTBackup to opening a Notepad window.
I know that NTbackup also logs into SQL Server because how else would it know about databases not set to Simple mode that it logs in the Event log? Even then wouldn't it use the account defined for the task and not SYSTEM? It does for normal file backups. This too is confusing as I believe that it does not backup SQL Server databases, at least I could find no documentation that it does.
Why does Index Server require sysadmin permission, if it is reading data to index, why can't it use db_datareader for the databases that it is configured to index?
Personally I think that this is a huge security hole.
Thanks,
Andy
-
When you say the "Scheduled Tasks application" are you referring to taskmgr.exe or to the "microsoft search" (mssearch.exe)? If the former, then your statement is incorrect as the taskmgr.exe does not login to SQL Server. If the latter, then yes it is correct and "by design" that the "Microsoft Search" services logs into SQL Server 2000 using the system account and builtin\admintrators (or [NT Authority\System]). This is "by design" behavior for SQL Server 2000 and MSSearch, and keep in mind that this design was in place since SQL Server 7.0 and when security concerns of this type were less known. However, this by design behavior has been changed for SQL Server 2005 and the new msftesql service uses the same startup account as SQL Server 2005.
NTBackup is a file level backup utility and at most would only backup the *.mdf, *.ndf and *.ldf files if and only if the MSSQLServer services was stopped as it maintains an exclusive lock on these files.
Finally, keep in mind that the "Index Server" is NOT the "Microsoft Search" (mssearch.exe) service as the two are separate products, but still use the same basic technology. As I said earlier, the behavior for "microsoft search" (mssearch.exe) is by design for SQL Server 2000 that has been changed for SQL Server 2005.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane -
John,
Thanks for responding. I understand the legacy situation for the mssearch.exe, sorry for calling it Index Server. Glad to hear that this security hole has been addressed in SQL05.
Yes the shortcut "Scheduled Tasks" is taskmgr.exe, and as I tried to explain, I think that I have proven that it does login to SQL Server using the SYSTEM account. I have Lumigent Entegra that audits the databases, including login failures. It showed a SYSTEM login failure with times that matched the task schedule, and as I adjusted the task schedule these Login failure events would also show the adjusted times. I disabled the mssearch.exe service to eliminate the possibility that it was the cause for the login failure. I changed the task from NTBackup to Notepad to eliminate it. So I was left with taskmgr.exe as the culprit. Do you know of any other default Win03 services that would be using the SYSTEM account to login to SQL Server, and would be triggered by taskmgr.exe launching a task, could it be Volume Shadow Copy service (VSS)?
As to NTBackup and SQL Server databases, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;828481
I see that more information was added to this article on 10/27/2005 that helps explain that NTBackup does backup Simple mode database files using VSS. At least that is what I got from reading this article. I am not trying to use NTBackup for SQL database backups, just trying to eliminate the Warning and Error event log entries.
More info on NTBackup problems and SQL Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;903643
Andy
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply