Problems Creating Full Text Index in MSSQL 2000 on Windows Server 2003
I recently needed to add a full text index on a table in a Microsoft SQL database. However, any time I tried to enable full text indexing on my database I received this obnoxious error: “The Microsoft Search service cannot be administered under the present user account.”
I Googled this message and didn’t come up with anything that seemed to help. I was able to deduce that this message (and several related messages) crop up when the user account under which MSSQL is running doesn’t have permission to access the Microsoft Search service.
Most of the advice I saw was to access the SQL Server properties though Enterprise Manager. From this, click on the Security tab and change the startup account to the System account and restart the SQL server. After this, you needed to repeat this process and change it back to the user account you want to run the SQL server under.
The reason you’re supposed to do this is that apparently when you change the account under which SQL Server runs from the properties panel, SQL Server adds the provided user account as an administrator for the Search service. Most of these problems seem to come from changing the SQL Server user account in from the Services control panel, which will not cause the Search service to be updated correctly.
This was a distinct possibility for my server so I tried this. Unfortunately it didn’t work.
After a bit more creative Googling, I found this link, which unfortunately requires you to register and log in. So, after registering (and probably committing myself to a life of even more spam), I logged in and read what turned out to be a message board post.
About half way down someone named “georgedo” posted the following steps to follow. There was no explanation as to what these steps were supposed to actually do. I decided to blindly follow the instructions and see what happened. (You might not want to try this at home.)
The steps were as follows:
- Search for the file named “ftsetup.exe” on the SQL server and note it’s location. I found mine under “C:Program FilesMicrosoft SQL ServerMSSQLBinn”.
- Open a command prompt to the path found in step 1.
- Execute this command: “ftsetup.exe sqlserver 0 1 0 0 0 4 “. I have no idea what this is supposed to do.
- The instructions said to note the result from this command. I don’t know why. It didn’t seem to matter. Mine returned this message: “FulltextSetup returned error code: 0x0”.
- At this point I diverged slightly from the instructions. I did a search for the file “sqlfth75.dll ” and noted it’s location.
- Open a command prompt to the path found in step 5.
- Run this command: “regsvr32 sqlfth75.dll “
- Restart the Microsoft Search and MSSQLSERVER services.
I followed those steps. Much to my amazement, it worked!
So, the moral of the story is that you should blindly trust people on the web who seem to know what they’re talking about. Well maybe not. Luckily, in this case it worked out for me. If you’re having the same problem you might want to try this too. If you have problems, track down “georgedo” and pester him!