Full Text Indexing Option Disabled

i, like hundreds of other people, am unable to use the Full Text indexing
feature because of the bug in SQL Server Enterprise Manager that disables
the ability to configure it.
See the Microsoft Knowledge Base article, but realize that it doesn't apply.
http://support.microsoft.com/?kbid=270671
It suggests that the problem is due to being logged onto your client
computer using a local account. It implies that because the account is local
to your computer (as opposed to being a domain account), you are not able to
query the state of the MSSearch service on the target SQL Server machine.
When Enterprise Manager is unable to query the state of the MSSearch on the
target SQL Server machine, it assumes the MSSearch service on the target SQL
Server machine is either disabled or not installed - and therefore prevents
you from configuring Full-Text indexing.
Problem is, we are not logged on using a local account; we are logged on
using a domain account. What is more, the domain account we are using is a
domain administrative account - the account has full ability to query state
of services on the remote SQL Server machine.
The we are experiencing has nothing to do with logging onto the client
computer using a local account. So what does it have to do with? What should
the knowledge base article say about this problem? What is the actual
problem?
i know this bug was introduced in Enterprise Manager with 2000sp2. Does
anyone know if this will be fixed in an upcoming service pack?
References:
http://support.microsoft.com/?kbid=270671
http://tinyurl.com/2wseqa
http://tinyurl.com/2mj8aj
http://tinyurl.com/3aqhea
http://tinyurl.com/35gf54
http://tinyurl.com/384wq7
http://tinyurl.com/3bmnab
http://tinyurl.com/2spgfq
http://tinyurl.com/3df22d
Keywords:
SQL Server Enterprise Manager Full-Text Indexing option disabled grayed out
greyed out "Full-Text Indexing..." full text indexing disabled sqlem, cannot
configure full text, fulltext disabled, fulltext indexing feature disabled,
howto configure fulltext indexing menu item disabled
did you try this?
http://www.indexserverfaq.com/TableNotEnabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:OQDnYS1RHHA.488@.TK2MSFTNGP06.phx.gbl...
> i, like hundreds of other people, am unable to use the Full Text indexing
> feature because of the bug in SQL Server Enterprise Manager that disables
> the ability to configure it.
> See the Microsoft Knowledge Base article, but realize that it doesn't
> apply.
> http://support.microsoft.com/?kbid=270671
>
> It suggests that the problem is due to being logged onto your client
> computer using a local account. It implies that because the account is
> local to your computer (as opposed to being a domain account), you are not
> able to query the state of the MSSearch service on the target SQL Server
> machine. When Enterprise Manager is unable to query the state of the
> MSSearch on the target SQL Server machine, it assumes the MSSearch service
> on the target SQL Server machine is either disabled or not installed - and
> therefore prevents you from configuring Full-Text indexing.
> Problem is, we are not logged on using a local account; we are logged on
> using a domain account. What is more, the domain account we are using is a
> domain administrative account - the account has full ability to query
> state of services on the remote SQL Server machine.
> The we are experiencing has nothing to do with logging onto the client
> computer using a local account. So what does it have to do with? What
> should the knowledge base article say about this problem? What is the
> actual problem?
> i know this bug was introduced in Enterprise Manager with 2000sp2. Does
> anyone know if this will be fixed in an upcoming service pack?
> References:
> http://support.microsoft.com/?kbid=270671
> http://tinyurl.com/2wseqa
> http://tinyurl.com/2mj8aj
> http://tinyurl.com/3aqhea
> http://tinyurl.com/35gf54
> http://tinyurl.com/384wq7
> http://tinyurl.com/3bmnab
> http://tinyurl.com/2spgfq
> http://tinyurl.com/3df22d
> Keywords:
> SQL Server Enterprise Manager Full-Text Indexing option disabled grayed
> out greyed out "Full-Text Indexing..." full text indexing disabled sqlem,
> cannot configure full text, fulltext disabled, fulltext indexing feature
> disabled, howto configure fulltext indexing menu item disabled
>
>
|||Hello Ian,
Its not just permissions but also firewalls that can get in the way. I have
this and am pretty sure it was looking at my local machine.
Run EM on the server itself should allow you to create your indexes.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> i, like hundreds of other people, am unable to use the Full Text
> indexing feature because of the bug in SQL Server Enterprise Manager
> that disables the ability to configure it.
> See the Microsoft Knowledge Base article, but realize that it doesn't
> apply. http://support.microsoft.com/?kbid=270671
> It suggests that the problem is due to being logged onto your client
> computer using a local account. It implies that because the account is
> local to your computer (as opposed to being a domain account), you are
> not able to query the state of the MSSearch service on the target SQL
> Server machine. When Enterprise Manager is unable to query the state
> of the MSSearch on the target SQL Server machine, it assumes the
> MSSearch service on the target SQL Server machine is either disabled
> or not installed - and therefore prevents you from configuring
> Full-Text indexing.
> Problem is, we are not logged on using a local account; we are logged
> on using a domain account. What is more, the domain account we are
> using is a domain administrative account - the account has full
> ability to query state of services on the remote SQL Server machine.
> The we are experiencing has nothing to do with logging onto the client
> computer using a local account. So what does it have to do with? What
> should the knowledge base article say about this problem? What is the
> actual problem?
> i know this bug was introduced in Enterprise Manager with 2000sp2.
> Does anyone know if this will be fixed in an upcoming service pack?
> References:
> http://support.microsoft.com/?kbid=270671
> http://tinyurl.com/2wseqa
> http://tinyurl.com/2mj8aj
> http://tinyurl.com/3aqhea
> http://tinyurl.com/35gf54
> http://tinyurl.com/384wq7
> http://tinyurl.com/3bmnab
> http://tinyurl.com/2spgfq
> http://tinyurl.com/3df22d
> Keywords:
> SQL Server Enterprise Manager Full-Text Indexing option disabled
> grayed out
> greyed out "Full-Text Indexing..." full text indexing disabled sqlem,
> cannot
> configure full text, fulltext disabled, fulltext indexing feature
> disabled,
> howto configure fulltext indexing menu item disabled
|||> did you try this?
> http://www.indexserverfaq.com/TableNotEnabled.htm
Yes.
|||> Its not just permissions but also firewalls that can get in the way. I
> have this and am pretty sure it was looking at my local machine.
The windows firewall is not enabled on my Windows XP SP2 machine. The server
is Windows 2000.

> Run EM on the server itself should allow you to create your indexes.
This, oddly enough, works.
Thank you.
|||Ian,
Make sure your polling interval is turned on in EM. To do this,
select Tools --> Options... and check the box for "Server state
polling".
If polling is not on, EM can't check to see if the MS Search service
is running. This results in the options being grayed out.
This only works on the server where SQL Server is intalled. As a
general rule, I turn this off for client machines, as it generates a
lot of network traffic, especially if you have a bunch of servers
defined in EM.
Hope this works. I spent days following all the instructions out
there from MS and the groups. Finally figured it out after comparing
registry entries for a server that worked and one that didn't.
Rick
On Feb 4, 3:29 pm, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Ian,
> Its not just permissions but also firewalls that can get in the way. I have
> this and am pretty sure it was looking at my local machine.
> Run EM on the server itself should allow you to create your indexes.
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
>
>
>
>
> - Show quoted text -

Full Text Index not working when populated from nvarchar. Bug?

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.

To re-create the problem quickly...

If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.

However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.

Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.

I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).

The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...

-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO

-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO

-- The below single entry WILL BE FOUND (the text source is being entered directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')

-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html')
GO

-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO

-- NOTE: You might need to give the catalog a chance to build before running the script below.

-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')Varbinary is supported in FT see these links:
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features,-part-2/ and http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx|||Hi Satya,

I know varbinary is supported by FT, but if you run my example code you'll see it has problems when an nvarchar is being converted into varbinary. Those rows of data don't show us when you do a CONTAINS or FREETEXT search.

This is my problem as I need to take nvarchars in order allow full multi-lingual functionality support.

Regards,
Gavin.|||I've also tried adding the following extra meta tag - <META http-equiv="Content-Type" content="text/html; charset=utf-16">

But this seemed to break the Full Text Index even further. If applied to the example T-SQL I give above then even the non-nvarchar input is no longer returned:

INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type" content="text/html; charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')

Full Text Index not working when populated from nvarchar. Bug?

Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no
results are ever returned due to the text column being cataloged being of
varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating
full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to
'.txt' in case it was a fault with the html ifilter but the problem persists
so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table
holds multi-lingual text and I'm adding a html meta tag <META
NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text
indexing word breaker to select the correct language to catalog the text
with. The aim being to provide more relevant searches in users native
languages from a single table (I've read a few articles that describe this
technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
Below is a T-SQL script you can run to demonstrate the effect I'm
experiencing...
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered
directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'),
'.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken
from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry
2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry
3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN
documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running
the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS
RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE
CONTAINS(varbinarycol, 'test')
I don't believe you need to use nvarchar(max) here, varchar(max) will work.
HTML will have a code page or utf metatag in it, which the html ifilter will
understand.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gavin" <gavin@.discussions.microsoft.com> wrote in message
news:3CDA0C57-863D-43CE-898C-B0F18DEA7A4E@.microsoft.com...
> Hi, I was wondering if any SQL Server gurus out there could help me...
> I have a table I'm trying to apply a full text catalog to, however no
> results are ever returned due to the text column being cataloged being of
> varbinary(max) that's being populated from a converted nvarchar(max)
> value.
> To re-create the problem quickly...
> If I populate the column via
> CONVERT(varbinary(max), 'test text')
> then there is no problem, I get results as expected.
> However if I populate the column via
> CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
> no results are ever returned.
> Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily
> creating
> full text catalogs when an nvarchar is not getting converted into a
> varbinary.
> I'm setting the Document Type column to '.html' (I've tried changing this
> to
> '.txt' in case it was a fault with the html ifilter but the problem
> persists
> so I believe I can rule this out).
> The reason I need to convert an nvarchar to varbinary is that the table
> holds multi-lingual text and I'm adding a html meta tag <META
> NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text
> indexing word breaker to select the correct language to catalog the text
> with. The aim being to provide more relevant searches in users native
> languages from a single table (I've read a few articles that describe this
> technique, but it's the first time I've tried to apply it).
> Any pointers / suggestions would be greatly appreciated. Cheers,
> Gavin.
> Below is a T-SQL script you can run to demonstrate the effect I'm
> experiencing...
> -- Create test database
> CREATE DATABASE FullTextTest
> GO
> USE FullTextTest
> GO
> -- Create test data table
> CREATE TABLE TestTable
> (
> pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
> varbinarycol VARBINARY(MAX),
> documentExtension VARCHAR(5),
> )
> GO
> -- The below single entry WILL BE FOUND (the text source is being entered
> directly)
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry
> 1'),
> '.html')
> -- The bellow two entries below WILL NOT BE FOUND (the text source is
> taken
> from an NVARCHAR(MAX) value)
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test
> entry
> 2' AS NVARCHAR(MAX))), '.html')
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test
> entry
> 3' AS NVARCHAR(MAX))), '.html')
> GO
> -- Create the full text catalog
> sp_fulltext_database 'enable'
> GO
> CREATE FULLTEXT CATALOG TEST AS DEFAULT
> GO
> CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN
> documentExtension LANGUAGE 1033)
> KEY INDEX tablePK
> GO
> -- NOTE: You might need to give the catalog a chance to build before
> running
> the script below.
> -- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS
> RETURNED
> SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE
> CONTAINS(varbinarycol, 'test')
|||Hi Hilary,
Thanks for the input, but I was under the impression from what I've read so
far that I would need to use nvarchar to future proof the input for the table
for when I start translating my application into Japanese, Chinese, and other
non-latin based scripts? Can store all these inputs safely in a varchar
variable? I was under the impression nvarchar had been introduced to get
around the language support limitation of varchars? I'm still on a learning
curve here so please tell me if I'm wrong?
I'm actually new to multi-lingual applications, but perhaps I should provide
more detail of what I'm trying to achieve...
I want to store multiple languages in the same table. In order to get the
full text index to use the correct word breakers on a row by row basis the
extra html meta tag is being added to the text to describe the language the
ifilter should go and fetch the relevant word breaker for. From what I
understand, this is only possible with varbinary data so I'm trying to
populate it with a passed in nvarchar parameter to my stored procedure. By
default sql server expects a single language on a table and this technique
should allow me to circumnavigate this limitation. The technique is described
in this article here -
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
(look for the heading "Via language tags in the content being indexed" about
a quarter of the way down the article).
So at the end of the day: I'm storing plain text (in any language), adding
an html meta tag to describe the language and pretending it's html to get SQL
Server to index based on language on a row by row basis.
Regards,
Gavin.
|||Hi Hilary, just woke up a bit more and made the connection - you wrote the
article I was using as my inspiration! Nice article by the way ;)
|||I've also tried adding the following extra meta tag - <META
http-equiv="Content-Type" content="text/html; charset=utf-16">
But this seemed to break the Full Text Index even further. If applied to the
example T-SQL I give above then even the non-nvarchar input is no longer
returned:
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type" content="text/html;
charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
|||You need to complete html document, not just the metatags. here is an
example which works for me.
CREATE DATABASE CODE_SAMPLE5
GO
USE CODE_SAMPLE5
GO
CREATE TABLE CODE_SAMPLE5
(PK INT NOT NULL IDENTITY CONSTRAINT PRIMARYKEY PRIMARY KEY,
VARBINARYCOL VARBINARY(MAX),
DOCUMENTTYPECOLUMN CHAR(4))
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE5 (VARBINARYCOL TYPE COLUMN
DOCUMENTTYPECOLUMN) KEY INDEX PRIMARYKEY
GO
--The first document is an html file. We are building this on the fly. Note
the title metatag, and the
--keywords metatag.
INSERT INTO CODE_SAMPLE5(VARBINARYCOL,
DOCUMENTTYPECOLUMN)VALUES(CONVERT(VARBINARY(500),' <HTML><HEAD><TITLE>this is
the title</TITLE><meta name=''keywords''
content=''keyword''></HEAD><BODY>test</BODY></HTML>'),'.htm')
SELECT* from code_sample5 where contains(*,'test')
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gavin" <gavin@.discussions.microsoft.com> wrote in message
news:026EDE33-B2EC-4761-AADE-A79EA13AA4F4@.microsoft.com...
> I've also tried adding the following extra meta tag - <META
> http-equiv="Content-Type" content="text/html; charset=utf-16">
> But this seemed to break the Full Text Index even further. If applied to
> the
> example T-SQL I give above then even the non-nvarchar input is no longer
> returned:
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type"
> content="text/html;
> charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'),
> '.html')
|||Hi Hilary,
I've also tried playing around with a full set of html wrapping tags as you
give in your example. I still get the same problem. If you tweak you're code
a little I believe you will experience the same problem. Convert your text
into an nvarchar before converting to varbinary and you'll see.
-- Ensure input is NVARCHAR
DECLARE @.Text nvarchar(max)
SET @.Text = '<HTML><HEAD><TITLE>this is
the title</TITLE><meta name=''keywords''
content=''keyword''></HEAD><BODY>test</BODY></HTML>'
-- Insert
INSERT INTO CODE_SAMPLE5(VARBINARYCOL,
DOCUMENTTYPECOLUMN)VALUES(CONVERT(VARBINARY(500),@. Text),'.htm')
Regards,
Gavin.
|||Have you checked the FTS crawl log for possible errors?
ML
http://milambda.blogspot.com/