Indexing Service

[DanielAc]

When I do a search for Model E for example in my search I get too many irelevant returns, when I quote it like "Model E" I get;


Microsoft OLE DB Provider for Indexing Service error '80041605'
The query contained only ignored words.
/search-results.asp, line 61


What can I do to fix this so that someone who is looking for a *Model E* Synth will find it?

Search code is similar to:

sSearchString = chr(34) & Request.Form("txtSearchFor") & chr(34)

strHomeURL = "/home"
strOutdoorURL = "/outdoor"
strMusicURL = "/music"
strCaseURL = "/protective-case"

sSql = "SELECT DocTitle, Path, Rank, FileName, Characterization, Size, vPath" & _
" FROM SCOPE( ' DEEP TRAVERSAL OF """ & strHomeURL & """ ' , ' DEEP TRAVERSAL OF """ & strOutdoorURL & """ ' , ' DEEP TRAVERSAL OF """ & strCaseURL & """ ' , ' DEEP TRAVERSAL OF """ & strMusicURL & """ ')" & _
" WHERE FileName LIKE '%.asp%' AND FREETEXT ( ' " & Replace(sSearchString, "'", """") & " ' ) > 0" & _
" ORDER BY Rank DESC"

[Dark_Raver]

I understand that you put your search words in quotes to make it a search phrase.
the problem is that you are forming your query in a string, having quotes in it, and the text with quotes in the field you enter screws that up.
since i can't tell for sure what DB you are using i can't say specifically but one way out i can think of would be to use command objects.

[DanielAc]

I am using SQL Server 2000 for the database.

To tell you the truth, I do not know how this line got in there
sSearchString = chr(34) & Request.Form("txtSearchFor") & chr(34)

As it is not in the original instructions I found in Active Server Pages 3.0 by Wrox.

I will try testing without that line and see what happens. When I test without that line I get.

Microsoft OLE DB Provider for Indexing Service error '80041605'

The query contained only ignored words.

/search-results.asp, line 54

[DanielAc]

On another part of the code.

I thought it would be nice to simplify, and use a Predefined View instead of calling out the columns.

But how do I code it so I do not have 2 *FROM* clause?

' sSql = "SELECT DocTitle, Path, Rank, FileName, Characterization, Size, vPath" & _

sSql = "SELECT * FROM WEBINFO" & _

" FROM SCOPE( ' DEEP TRAVERSAL OF """ & strHomeURL & """ ' , ' DEEP TRAVERSAL OF """ & strOutdoorURL & """ ' , ' DEEP TRAVERSAL OF """ & strCaseURL & """ ' , ' DEEP TRAVERSAL OF """ & strMusicURL & """ ')" & _
" WHERE FileName LIKE '%.asp%' AND FREETEXT ( '" & Replace(sSearchString, "'", """") & "' ) > 0" & _
" ORDER BY Rank DESC"

[Dark_Raver]

I am quite positive that your problem is caused by the quotes used to make the search a phrase.

having more than one from ... that is called a joint and has to be done on tables that share a similar column.
ie.

SELECT a.Name, a.Surname, b.Address
FROM Names a, Addresses b
WHERE a.PersonID = b.PersonID

a and b are aliases for the tables to save you typing in case your query ends up being a lot longer, it can be just about anything.
now there is more to this than you see here and you can output a lot of useless data if you mess these up.

also seeing that you are using SQL Server 2k, you are most likely better off using command objects.

both of these issues are too big to discuss here but a good book or two (even old ones) can give you a heads up on all these and might come in handy as a good reference anyways.

[DanielAc]

:)

I have The Guru's Guide to Transact-SQL is that the kind of book you have in mind, or are you thinking more along the lines of SQL SERVER 2000?

[Dark_Raver]

I looked up some info on the book you mentioned and it sounds like a good one to have. It is sopposed to concentrate more on the MS version of SQL, which is a bit different than what you would get in oracle and other non MS DB's.
SQL is SQL no matter which DB it is, there are minute differences between them but once you know how to do something it isn't hard to adjust.

unfortunately i can't recoment any books on command objects cause i did it in school and it was all lectures really, plus the way to do it is language dependent. but if you have a book for the language you are using ( you haven't really mentioned this ) it might very well have info on this. Also there is more than likely info and/or examples in the msdn.
unfortunately it is not something you can pick up over night and will require time to learn/master, it will however improve the performance, security and portability of the code.

If you are seriously interested in learning these i would strongly suggest taking a class since these aren't too easy to figure out on your own. But a good book might be just as able, it just might not answer specifc questions you might have.