MS SQL Full-Text Search and Query Analyzer = 0 rows affected???

I run the following SQL syntax without error, however no columns fill with data that I know exists......

Q. Why do NOT recieve any data displayed when running the following workable queries in MS Query Analyzer?

SELECT ArticleURL
FROM Article
WHERE CONTAINS(ArticleTitle, 'beginning OR magazine')

SELECT * FROM Article WHERE contains(ArticleDescription, ' formsof (inflectional, magazine) ')

SELECT * FROM Article WHERE freetext(*, 'magazine')
LVL 1
kvnsdrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rafranciscoCommented:
Try this one:

SELECT ArticleURL
FROM Article
WHERE CONTAINS(ArticleTitle, ' "beginning" OR "magazine" ')
rafranciscoCommented:
To verify if there are records that should be returned, try this query:

SELECT ArticleURL FROM Article
WHERE ArticleTitle LIKE '%beginning%'

SELECT ArticleURL FROM Article
WHERE ArticleTitle LIKE '%magazine%'

SELECT ArticleURL FROM Article
WHERE ArticleDescription LIKE '%magazine%'
kvnsdrAuthor Commented:
Substituing CONTAINS for LIKE works, however;

Q. How is a programmer supposed to fully test MS-SQL Full-Text Search code using the correct terms like "Contains", "ContainsTable", etc.......
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rafranciscoCommented:
Just to be sure we are on the same page, did you define Full-Text Indexing in your Article table?
kvnsdrAuthor Commented:
Yes, otherwise I get an error message from Query Analyzer that the word CONTAINS, etc.. cannot be run..........
rafranciscoCommented:
Ok, let's start with CONTAINS.  Does this output anything?

SELECT *
FROM Article
WHERE CONTAINS(ArticleTitle, 'magazine')

SELECT *
FROM Article
WHERE CONTAINS(ArticleDescription, 'magazine')
kvnsdrAuthor Commented:
No, only all the column headers, no data, either code......

SELECT ArticleTitle
FROM Article
WHERE CONTAINS(ArticleTitle, 'magazine')

SELECT *
FROM Article
WHERE CONTAINS(ArticleDescription, 'magazine')
rafranciscoCommented:
How about this:

SELECT ArticleTitle
FROM Article
WHERE CONTAINS(ArticleTitle, ' "magazine*" ')

SELECT *
FROM Article
WHERE CONTAINS(ArticleDescription, ' "magazine*" ')
kvnsdrAuthor Commented:
Still no data showing, I don't get it........

the word magazine is definetly in the ArticleTitle and ArticleDescription columns.
rafranciscoCommented:
According to Books Online, using the CONTAINS is case-sensitive.  Can you try these queries and check how the word "magazine" is stored in terms of capitalization:

SELECT * FROM Article
WHERE ArticleTitle LIKE '%magazine%'

SELECT * FROM Article
WHERE ArticleDescription LIKE '%magazine%'
kvnsdrAuthor Commented:
I stored everything is lower case to keep things simple.....

Yes the LIKE statements always work, but CONTAINS does not.........
rafranciscoCommented:
Ok, I think I know what the problem is.  Go to Enterprise Manager and right-click on your table (in this case the Article table).  Select "Full-Text Index Table" --> "Start Full Population".  Once this is done, run your query again.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvnsdrAuthor Commented:
I just did that and setup the scheduler as well. I'll let you know..........
Anthony PerkinsCommented:
rafrancisco,

>>According to Books Online, using the CONTAINS is case-sensitive.  <<
Not unless the database is case-sensitive.

kvnsdr,
I suggest you read up on Full-Text Search.  While it is far more versatile and faster than using LIKE it does require maintaining the catalog either through Full or Incremental population EVERY time an item is added or it will not show up.  Depending on your version of SQL Server this can be handled in the background, but there is always a delay before an item is added.
kvnsdrAuthor Commented:
I found an expert at www.sqlservercentral.com and here is his post:

Well, I am the expert on SQL Server Full-text Search (FTS) as I have worked with it while at Microsoft since its inclusion in SQL Server 7.0 Beta3 in 1998. I maintain a blog on this subject as well as have extensively research this topic for a book on the wider topic of Search and I've made thousands (well, maybe hundreds  ) of replies in the sql server public newsgroup (microsoft.public.sqlserver.fulltext).

SQL FTS is a language-specifc "word" based search algorithm, while LIKE is a "character" based algorithm. Both methods have their advantages and disadvantages, and SQL FTS was designed to provide different text search methods and the two method can and do return different results based upon the search string as well as the search text and OS platform.

From a performance perspective, when you have table that has 100,000+ to over 1 million rows and if you continuously run the below LIKE query using LIKE '%word%', each query will do a table or index scan (an expensive operation) for each query regardless of the indexes on the table or column. FTS queries with on a FT enable table with the same number of rows will perform better on average and depending upon the exact query and data volume
than a similar T-SQL LIKE query.


SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

SQL Server 2000 FTS on Windows 2000 vs. Windows Server 2003...
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!158.entry

See the SQL FTS blog link for more information on SQL Server 2000 as well as SQL Server 2005 Full Text Search.

Thanks,
John

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane
Anthony PerkinsCommented:
All of that is well and true, but does it solve your immediate problem?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.