Full Text

[gbaughma]

So I must be doing something wrong.
I am trying to set up Full Text search on a database with a few hundred thousand records.
I have created the catalog, defined the index using the wizard, selected the columns I wanted to be able to search on, and waited the hours to create the index.

When I do a query like this, however:
SELECT * FROM MyTableName WHERE FREETEXT(*,'GIRL SCOUTS')

I get 0 records.
I should be getting a crapload of records.

I've also tried CONTAINS(*,'GIRL') and still got nothing.

Any thoughts? I must be missing something simple and stupid.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Oh... SQL Server 2008 Enterprise... (I know that's going to be the first thing asked... lol)


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SQLBill]

I haven't used Full Text search, but is it possible your database is case sensitive and that is affecting the search? Have you tried:

SELECT * FROM MyTableName WHERE FREETEXT(*,'Girl Scouts')

-sqlbill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gbaughma]

It's all caps in the fields... and it *shouldn't* matter... that's kind of the idea behind full text search...
<sighs>
But I did try your suggestion with the same results.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

What is the compatibility level for your database?

SELECT compatibility_level
FROM sys.databases
where name = 'YourDatabaseNameHere'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

80


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

Compatibility level = SQL2000. I suggest you try changing it to something higher, if you can. I haven't seen any documentation that the compatibility level affects full text indexing, but you never know.

I did recently read documentation that full text searches are significantly different between SQL2005 and SQL2008.

Changing the compatibility level can affect other aspects of the server. In fact, it could cause certain things to fail completely. If you can, I would suggest that you backup your database, restore it to a new DB and then change the compatibility level of the restored DB to see if this makes any difference.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Changed compatibility to 100
Rebuilt the index
Still no search results.
I don't get what I'm doing wrong. <Sigh>


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

What do you get when you run this?

SELECT SERVERPROPERTY('IsFullTextInstalled')
SELECT FULLTEXTSERVICEPROPERTY('ISFULLTEXTINSTALLED')

Ideally, both should return 1.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Both returned 1.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

And, magically, it started working.

*SMH*


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com