Full-index Search Problem... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Full-index Search Problem...

Author

Topic

kwilliams

194 Posts

Posted - 2003-09-16 : 09:47:25

Hello, and thanks in advancec,

I have a SQL Query (QUERY A):
SELECT *
FROM dbo.Commission_Minutes
WHERE Min_ID = MMColParam AND Date = 'varDate' OR FREETEXT(Minute, '%varKeywords%')
ORDER BY Date ASC

VARIABLES:
varDate % Request("txtDate")
varKeywords % (Request("txtKeywords")
MMColParam Min_ID Request.QueryString("id")

...that get's the following error message:
A clause of the query contained only ignored words.

I don't understand why I'm getting this error message, since there are no noise words in the FREETEXT parethesis. If I do the query like this (QUERY B):
SELECT *
FROM dbo.Commission_Minutes
WHERE Min_ID = MMColParam AND Date = 'varDate' OR Minute LIKE '%varKeywords%'
ORDER BY Date ASC
(WITH THE SAME VARIABLES AS QUERY A)

...it works fine. But I ofcourse want to use the full-index search in SQL, so this query wouldn't utilize that tool.

If anyone can look at my first full-index search query (QUERY A), and see what I'm doing wrong, that would be great. Thanks.

KWilliams

MuffinMan
Posting Yak Master

107 Posts

Posted - 2003-09-16 : 12:40:05

Are you passing a single number in varKeywords? Full text indexing doesn't appear to support this. See examples below.

{*Also, shouldn't your WHERE clause have some parens, such as WHERE Min_ID = MMColParam AND (Date = 'varDate' OR FREETEXT(Minute, '%varKeywords%'))?}

declare @Param1 varchar(30), @Param2 varchar(30), @Param3 varchar(30)

select @Param1 = 'cincinnati' , @Param2 = ''
select @Param3 = '3'

SELECT
top 1 *
FROM dbo.Subscriber
WHERE city = @Param1 AND (state = @Param2 OR FREETEXT(address, @Param3))
)

/* result: ERROR:
"Execution of a full-text operation failed.
A clause of the query contained only ignored words."
*/

select @Param3 = '30'

SELECT
top 1 *
FROM dbo.Subscriber
WHERE city = @Param1 AND (state = @Param2 OR FREETEXT(address, @Param3))

/* result: Returns first record where address contains "30"
John, Smtih, 30 Main St, Cincinnati, OH, ........
*/

select @Param3 = 'rd'

SELECT
top 1 *
FROM dbo.Subscriber
WHERE city = @Param1 AND (state = @Param2 OR FREETEXT(address, @Param3))

/* result: Returns first record where address contains "rd"
Tom, Allen, 123 Maple Rd, Cincinnati, OH, ........
*/

kwilliams

194 Posts

Posted - 2003-09-16 : 13:03:12

Hi MuffinMan,

Thanks for the quick response. I throught that having the parenthesis was probably the solution, so I tried it like this:
SELECT *
FROM dbo.Commission_Minutes
WHERE Min_ID = MMColParam AND (Date = 'varDate' OR FREETEXT(Minute, '%varKeywords%'))
ORDER BY Date ASC

...but it still gave me that same error message. As for varKeywords, it's value comes from a text field from a form that collects one or more keywords. I have this set up on my current Search page, and it works great. But for some reason, I'm getting this error message on this page with the same setup.

This is how the 3 variables (including varKeywords) are set up before the RS:
<%@LANGUAGE="JAVASCRIPT"%>

<%
var rsMinutes__varDate = "%";
if(String(Request("txtDate")) != "undefined") {
rsMinutes__varDate = String(Request("txtDate"));
}
%>
<%
var rsMinutes__varKeywords = "%";
if(String(Request("txtKeywords")) != "undefined") {
rsMinutes__varKeywords = String(Request("txtKeywords"));
}
%>
<%
var rsMinutes__MMColParam = "Min_ID";
if(String(Request.QueryString("id")) != "undefined") {
rsMinutes__MMColParam = String(Request.QueryString("id"));
}
%>

Any more suggestions or ideas would be very much appreciated. Thanks MuffinMan.

KWilliams

kwilliams

194 Posts

Posted - 2003-09-16 : 15:46:53

I forgot to mention that you can see this problem in action at http://www.douglas-county.com/Local_Govt/minutes.asp. Just enter the keyword into the "Keyword(s)" field, and it will show you the error. If you enter 09/12/2003 into the "Date" field, a minute will come up correctly for that date. This is the SQL Statement that's I'm using on the Results page that you'll see:
<%
var rsMinutes__varDate = "%";
if(String(Request("txtDate")) != "undefined") {
rsMinutes__varDate = String(Request("txtDate"));
}
%>
<%
var rsMinutes__MMColParam = "Min_ID";
if(String(Request.QueryString("id")) != "undefined") {
rsMinutes__MMColParam = String(Request.QueryString("id"));
}
%>
<%
var rsMinutes__varKeywords = "%";
if(String(Request("txtKeywords")) != "undefined") {
rsMinutes__varKeywords = String(Request("txtKeywords"));
}
%>
<%
var rsMinutes = Server.CreateObject("ADODB.Recordset");
rsMinutes.ActiveConnection = MM_strConn_STRING;
rsMinutes.Source = "SELECT * FROM dbo.Commission_Minutes WHERE ([Date] = '"+ rsMinutes__varDate.replace(/'/g, "''") + "' OR FREETEXT(Minute,'%"+ rsMinutes__varKeywords.replace(/'/g, "''") + "%')) AND Min_ID = "+ rsMinutes__MMColParam.replace(/'/g, "''") + " ORDER BY Date ASC";
rsMinutes.CursorType = 0;
rsMinutes.CursorLocation = 2;
rsMinutes.LockType = 3;
rsMinutes.Open();
var rsMinutes_numRows = 0;
%>

Thanks.

KWilliams

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-17 : 06:46:39

just as a btw.....Error 404 - Page not found, etc comes up on that link (@ 11:45GMT 17/09/2003)

kwilliams

194 Posts

Posted - 2003-09-17 : 09:55:37

That's because the forum included the period in the end of the link. If you go to http://www.douglas-county.com/Local_Govt/minutes.asp, it will work. Thanks Andrew.

KWilliams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources