Solved

Cannot set up Full text catalogs - get "Property IsAccentSensitive is not available for FullTextCatalog"

Posted on 2009-07-16
18
5,407 Views
Last Modified: 2013-05-02
I am trying to set up a full text index on my database (for the first time, no pre-existing catalogs)

It will create the catalog fine, but if I open its properties I get the message:

"Property PopulationStatus is not available for FullTextCatalog '[forums]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)"

At the Full Text Catalog Properties diaglog, the properties window is greyed out, and whilst I can attempt to change tables/views, when I click OK to close the dialog I get :

"Cannot execute changes.

Property IsAccentSensitive is not available for FullTextCatalog '[forums]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)"

I have read the MS page:  http://support.microsoft.com/kb/910067
My database exhibits exactly the same characteristics, i.e. select FULLTEXTCATALOGPROPERTY('<CatalogName>','AccentSensitivity') produces a null - however the workaround (to detach and reattach the database) does not work.

I would be grateful for any suggestions as I am stuck! Thanks in advance!

0
Comment
Question by:Gazza72
  • 9
  • 8
18 Comments
 
LVL 14

Expert Comment

by:rob_farley
ID: 24892692
What kind of error are you getting when you detach and reattach?

Or are you meaning that detaching and reattaching doesn't seem to solve the problem?

Rob
0
 
LVL 1

Author Comment

by:Gazza72
ID: 24893147
Thanks for the response,

Detaching and reattaching doesn't seem to solve the problem.
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 24894406
That problem describes a situation where the catalog name is wrong. Can you perhaps drop it and recreate it?

Rob
0
 
LVL 1

Author Comment

by:Gazza72
ID: 24894503
I can remove the catalog and recreate it, and I still have the same problem.

As a test, I also created a completely new database, and the problem exists there as well.
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 24901997
Ok... can you show me the results of:
select * from sys.fulltext_catalogs

And can you also do:
select * from sys.databases

I'm also wondering if it's related to users set up for your services. If you run SQL Configuration Manager, can you see what users are being used for your services? Then can you use SQL Configuration Manager to change the users to something else and then set them back again?

(Just reminds me of a problem that a client had recently, where there were occasional permission problems because someone had changed a service account using Computer Management, not SQL Config Manager)

Rob

0
 
LVL 1

Author Comment

by:Gazza72
ID: 24902825
If I go into sql config manager I can see the users. As its a production machine, I cant try changing the account at the moment but It looks like I would be able to.

MSSQLServer is logged on as the nt account I created specifically for the server. HOWEVER integration services is logged on as  NT Authority\Network Service. I wonder if this might be a problem.

SQL output attached.

ee.txt
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 24903531
That NT Account - did you set that as the service account during the install, or using the Services section of Computer Management, or using the SQL Config Manager tool?

is_fulltext_enabled is only turned on on the msdb database. Can you run this please on your user databases?

sp_fulltext_database 'enable'

See if this does it for you...

Rob
0
 
LVL 1

Author Comment

by:Gazza72
ID: 24903586
Thanks for that,


sp_fulltext_database 'enable' : The SP worked fine, but the problem still exists. I tried removing and recreating the catalog, and the problem is still there.

I cannot remember exactly when i set the NT account up- but I am certain I created the account pre-install, then named it as part of the install.
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 24903627
So...

select name, is_fulltext_enabled
from sys.databases

...says that it's enabled on the databases you need it to be.

Can you run it on the system databases too? It's just that fultext is supposed to be enabled on all databases in SQL2008, and I'm wondering if it's that that's causing the database id syncing problem.

And... can you do the detach/attach thing again after each database has had that proc run on it?

Thanks,

Rob
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:Gazza72
ID: 24904153
Yes.

I cannot do it on the system databases:
Msg 9966, Level 16, State 1, Procedure sp_fulltext_database, Line 33
Cannot use full-text search in master, tempdb, or model database.

I detached the database, reattached. Still got the problem. Created a new one and that has the same problem too.

I am wondering if maybe the FT catalogs config is stored on disk somewhere where I have not granted permission for the service?
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 24910470
Ah - you're not storing it in the data folder then?

Make sure that your NT account (actually, the group that it's become part of) has full control of the folder that where you're storing the files.

Rob
0
 
LVL 1

Author Comment

by:Gazza72
ID: 24924458
I'm not sure where it is being stored, I saw a FTDATA folder, and assumed maybe that was it.
I've not told it to save them elsewhere, so I think its in the same filegroup as the database.
I've been checking all the permissions, i did find the nt account i was using was not a part of the sql group, so added it.
However i've still got the same problem.

Starting to think I'll have to re-install sql server to get round this.
0
 
LVL 14

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24932290
You shouldn't have to reinstall. But... I would try setting the users using SQL Config Manager. Then try recreating the full text catalogs completely. With SQL 2008, you shouldn't have issues with the file system if your service accounts were set using SQL Config Manager.

It is SQL 2008, right?

Also, what operating system are you using, and what account is MSSQLFDLauncher running as (check in SQL Config Manager)?

Rob
0
 
LVL 1

Author Comment

by:Gazza72
ID: 24936495
Yes, its sql 2008, running on windows 2008 server 64 bit.

MSSQLFDlauncher is not listed in the config manager-  

Hmm. I've just googled it, and it looks essential. This could the the problem!
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 24940058
Ah yes... could be. I think we've found the problem.
0
 
LVL 1

Author Comment

by:Gazza72
ID: 24942996
I've scheduled maintenance for tomorrow and will get this installed and  will let you know how it went
0
 
LVL 1

Author Comment

by:Gazza72
ID: 24945445
That indeed was the problem- the full text search feature was not added when SQL 2008 was installed.
I no longer get the error messages. Pity SQL wasn't smart enough to not let you create full text catalogs if the full text system is not installed!

Many thanks for your help- thats a big problem solved!

0
 

Expert Comment

by:Greg911P
ID: 39132526
Same here - identical problem - but with SQLSERVER2012..   feature wasnt installed - why didnt I think of this too?

Obvious once pointed out.  Thanks, this wil have saved me hours of pointless trial and error!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now