Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have created a catalog and fulltext index for my table as :
create fulltext catalog testsearch
CREATE FULLTEXT INDEX ON test_master
    (name)
    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 21:46pm
Edited 16-Dec-12 11:31am
RedDk9.5K
v2

1 solution

Rate this: bad
good
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
	CONTAINS([GeogCol2],@searchWord)
GO
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.
 
[edit]
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;
GO
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 FULLTEXT CATALOG rwood_131_FTCat
CREATE UNIQUE INDEX ui_131 ON [20121212141159365].[qanda].[fulltextSearchable](idx) 
CREATE FULLTEXT INDEX ON [20121212141159365].[qanda].[fulltextSearchable](
						[GeogCol2]
						)
						rwood_131_FTCat
						KEY INDEX ui_131 ON rwood_131_FTCat
						WITH CHANGE_TRACKING AUTO 
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.
  Permalink  
v6

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 487
1 CPallini 345
2 OriginalGriff 240
3 George Jonsson 159
4 Abdul Samad KP 145
0 OriginalGriff 6,329
1 Sergey Alexandrovich Kryukov 5,700
2 CPallini 4,940
3 George Jonsson 3,469
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 16 Dec 2012
Copyright © CodeProject, 1999-2014
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