Click here to Skip to main content
12,635,792 members (26,552 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server , +
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 22:46pm
Updated 16-Dec-12 12:31pm
RedDk14.7K
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
Top Experts
Last 24hrsThis month


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