Click here to Skip to main content
13,291,084 members (67,399 online)
Rate this:
Please Sign up or sign in to vote.
See more: , +
I have created a catalog and fulltext index for my table as :
create fulltext catalog testsearch

    KEY INDEX PK_test_master
    ON testsearch

But when I try to retrieve values as
select * from test_master where contains(name, 'product')

The query just returns me the columns of the table 'test_master' but no rows are populated.. I'm not getting any error either.. i have tried populating my index again and i am sure that i have a value 'product' in column 'name'.. plz help me
Posted 13-Dec-12 22:46pm
Updated 16-Dec-12 12:31pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

As long as you've managed fulltext index on your table
USE [20121212141159365]
DECLARE @searchWord [nvarchar](128)
SET @searchWord = N'POINT (-97.990837 26.159519)'
SELECT [GeogCol2]
  FROM [20121212141159365].[qanda].[fulltextSearchable] WHERE

Otherwise you'll get an error like this:

Msg 7601, Level 16, State 2, Line 6
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view [20121212141159365].[qanda].[fulltextSearchable] because it is not full-text indexed.

If the index is not set up correctly, you'll probably have encountered a message like this:

Msg 9928, Level 16, State 1, Line 1
Computed column 'GeogCol2' cannot be used for full-text search because it is nondeterministic or imprecise nonpersisted computed column.

Or something completely different:

Msg 7653, Level 16, State 1, Line 1
'--------' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

And if competancy is determined by something like a spelling error in the code, you might even encounter an error like:

Msg 7609, Level 17, State 5, Line 1
Full-Text Search is not installed, or a full-text component cannot be loaded.

Skip all of the above to verify quickly whether FULL-TEXT SEARCH is even installed:

EXEC sp_fulltext_service @action='upgrade_option', @value=1;

Except for the fact that the example I use here is oogusbay because geometry type is not full-text searchable by definition, using this form is expedient to understanding so I'll continue it here:
USE [20121212141159365]
CREATE UNIQUE INDEX ui_131 ON [20121212141159365].[qanda].[fulltextSearchable](idx) 
CREATE FULLTEXT INDEX ON [20121212141159365].[qanda].[fulltextSearchable](
						KEY INDEX ui_131 ON rwood_131_FTCat
SELECT * FROM sys.fulltext_indexes
[edit end]

That last bit should do the trick. That is, if you've managed to install FULL-TEXT SEARCH in the beginning.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.171207.1 | Last Updated 16 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100