No results from this query!

[jarla]

Hi!
I´ve just added "full text search" to our SQL Server 2000 -system. But I don´t know what is wrong with it because it don´t find any records with "Contains" -value. Here is one example query:

SELECT ID, asset_name, folder_name FROM picturedatabase WHERE Contains(categories, 'woman OR car')

I´ve also tried simple
SELECT ID, asset_name, folder_name FROM picturedatabase WHERE Contains(categories, 'cat') but with no results. Only "0 pictures found". I know that there are words "woman" and "car" I have also option to search with picturename in the same search and it works great. In the end of the search is line "OR asset_name LIKE 'blaablaa%'"
That line gives results normally. Search is also very fast, it takes only 1,5 seconds to search from 160.000 rows.
But what is wrong with that "Contains" -search. "Categories" -fieldtype is nvarchar(1000).

 

[vbkris]

SELECT ID, asset_name, folder_name FROM picturedatabase WHERE Contains(categories, 'woman') OR Contains(categories, 'car')



Known is handfull, Unknown is worldfull

 

[jarla]

No. Like I wrote, even this kind of search don´t find any records:
SELECT ID, asset_name, folder_name FROM picturedatabase WHERE Contains(categories, 'cat')

 

[vbkris]

sounds stupid but:
SELECT ID, asset_name, folder_name FROM picturedatabase WHERE Contains(categories, '%cat%')

Known is handfull, Unknown is worldfull

 

[roccorocks]

I see your problem now. Here is what the SQL Server Help has . . .

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO

Notice, you are missing the "" around your search criteria. Try this out, should work!

Rocco

 

[jarla]

Aarrrgh!
Still no results. I just tested some different queries. This works (but of course it´s DAMN slow):
SELECT ID, asset_name, folder_name from picturedatabase
WHERE categories like '%cat%'
(that gives 15026 records)

But this don´t work:
SELECT ID, asset_name, folder_name from picturedatabase
WHERE contains(categories, ' "cat" ')
(0 records found...)

I´ve tried with "" and without them...This is driving me grazy. Before I updated the SQL Server with newest service pack, everything worked. Well, at the same time I started from the "clean table" and I imported everything from Access -database again (our previous search system was Access -database). But I did everything like I did before new service pack and my contains -search was ok then.

 

[jarla]

Hi again!
Now it works! I changed the "nvarchar" to "varchar" and after that the "CONTAINS" -search worked normally... But I really didn´t know that "nvarchar" don´t work as good with "CONTAINS" -search than "varchar"...