Full text search query not returning result in SQL Server

Question

0.00/5 (No votes)

See more:

, +

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

harshad chawla

Updated 16-Dec-12 11:31am

RedDk

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RedDk · Answer 1 · 2012-12-14T09:08:00

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:

SQL

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.