Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
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
Edited 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
0 Peter Leow 160
1 Mika Wendelius 156
2 Abhinav S 155
3 Sergey Alexandrovich Kryukov 115
4 TheRealSteveJudge 114
0 Sergey Alexandrovich Kryukov 8,593
1 OriginalGriff 6,536
2 Peter Leow 3,727
3 Zoltán Zörgő 3,436
4 Richard MacCutchan 2,417

Advertise | Privacy | Mobile
Web01 | 2.8.150123.1 | Last Updated 16 Dec 2012
Copyright © CodeProject, 1999-2015
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