How to return FTS results from varchar(MAX) or Text data type column?

I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).

For instance this works, and will return all applicable results.

CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](1000) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED

SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);

And this does not. It returns zero results what so ever.

CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](MAX) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED

SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);

Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?

Did you create a fulltext index on the column and made it language specific / language neutral ?

Jens K. Suessmeyer

http://www.sqlserver2005.de