Why the Full-Text indexing option is greyed out?

Question

I installed SQL Server 2008 Express with Advanced Services, but when I try to create a new database, the option Full-Text indexing is greyed out, I believe the full-text indexing has been installed, because I did a query as below:

use [mydbname]
select fulltextserviceproperty('isfulltextinstalled') 

This query returns 1, so I think it has been successfully installed.

Full-text indexing is supported in MSSQL Express with Advanced Services edition, which I have installed. Page for reference:
http://www.microsoft.com/downloads/details.aspx?familyid=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=en

Natation

Answer 1

Make sure NAMED PIPES is enabled in the protocols in configuration manager as full text service needs this!

Allegorize

This was exactly the problem for me. Thank you! – Rogerson

Answer 2

Is the service started? I think a default install of 2008 Express has FTS stopped.

In 2005 Express (IIRC) you had to make the catalogs manually, rather than through managmement studio; you could try that and see if you get an error:

use MyDatabaseName
go
EXEC sp_fulltext_database 'enable'
go
CREATE FULLTEXT CATALOG MyFullTextCatalog

If you need to manually create the indexes you can do something like:

CREATE FULLTEXT INDEX ON MyDatabaseName.dbo.MyTableToSearch
(
MySearchColumn
Language 1033
)
KEY INDEX MyCurrentIndex;

Paoting

I did the query and it was executed with no problem. ----------------------------------- Command(s) completed successfully. – Natation

But after that, I check the Propertis -> Files, see the Full-text indexing option is still greyed. – Natation

It might just be a GUI thing. Can you see the catalog in Management Studio? You might need to manually create the indexes too. – Paoting

Answer 3

You can view all the full text enabled value for each DB with this code:

select name, DATABASEPROPERTY(name,'IsFulltextEnabled')
from master..sysdatabases where dbid > 4

Pollus

Elective

Answer 4

Coincidentally I was just reading a performance guide for FTS in SQL 2008 and came across this:

The New Database dialog box in Management Studio has an option grayed out. Just below the name and owner there is a grayed out check box. In the released version of SQL Server 2008 the full text options are on by default. This was left in place in case any customers had references to it in scripts.

So it looks like it's greyed out on purpose :)

Paoting

Answer 5

The page here gives information on how to confirm that you've installed full-text with the SQL Server install as well as steps to install it after the fact.

This page has a decent walk-through of setting it all up.

Also, make sure that the service is running.

Hopefully one of them will point you in the right direction.

Tonnage

Answer 6

The following list highlights the major SQL Server components that are not supported in SQL Server Express:

• Reporting Services
• Notification Services
• Integration Services
• Analysis Services
• Full text search
• OLAP Services / Data Mining

From: http://msdn.microsoft.com/en-us/library/ms165636.aspx

Raseda

My edition is SQL Server 2008 Express with Advanced Services, this edition supports Full-Text indexing, page for reference: microsoft.com/downloads/… – Natation

Agreed, as long as the Advanced Services version is installed then FTS works fine. This page details what 2008 Express supports in detail msdn.microsoft.com/en-us/library/ms365248.aspx – Gastrointestinal

Answer 7

Beware of current compatibility level set in your DB when configuring SQL Server Fulltext

In case it might help people having the same issues I found, I'm posting this here because it's related to the question.

I had a SQL Server DB installed by an external company. We asked for some modification to its software that required adding fulltext search features in the database.

I had a test database that I had created from scratch beside this company database to test the configuration of these services.

When I tried to create a Fulltext catalog in SQL Server 2008 all options where greyed out for the company's database, whereas in the database created from scratch everything was OK, the screen was not greyed out and I could for example state that I wanted accents to be ignored.

Out of despair, I started to compare every parameter between the two databases, and I found that that the company's database compatibility level was set to 'Sql Server 2000 (80)'. As soon as I changed that to 'SQL Server 2008 (100)', everything started to work fine, the Fulltext catalog creation scren was no longer greyed out.

I found a note, in this article somehow related to this compatibility issue: https://msdn.microsoft.com/en-us/library/ms142583.aspx#OV_ft_predicates

Chuckchuckfull