ignored words in FUll Text indexing problem

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)

ignored words in FUll Text indexing problem

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2000-12-19 : 22:54:28

Hi there,

I have the following procedure that searches content (text field) and title (varchar) of multiple tables in my database:

CREATE PROCEDURE p_SEARCH_UNION_all

@keywords varchar(255)
@keywordsContent varchar(255)

AS

DECLARE @mySQL varchar(6000)

SET @mySQL = "Select newsid AS RecordID, title as TITLE, admin_id as ADMINID, content AS CONTENT, 'news.asp' AS URL From news"
SET @mySQL = @mySQL + " WHERE title like '%" + @keywords + "%' OR "
SET @mySQL = @mySQL + "CONTAINS (CONTENT, '" + @keywordsContent + "')"
SET @mySQL = @mySQL + " UNION ALL "

SET @mySQL = @mySQL + "SELECT whatsnew_id AS RecordID, title as TITLE, admin_id as ADMINID, content AS CONTENT, 'whatsnew.asp' AS URL FROM whatsnew"
SET @mySQL = @mySQL + "WHERE title like '%" + @keywords + "%' OR "
SET @mySQL = @mySQL + "CONTAINS (CONTENT, '" + @keywordsContent + "')"
SET @mySQL = @mySQL + " UNION ALL "

SET @mySQL = @mySQL + "SELECT asxid AS RecordID, title as TITLE, admin_id as ADMINID, content AS CONTENT, 'asx.asp' AS URL FROM asx_announcements"
SET @mySQL = @mySQL + " WHERE title like '%" + @keywords + "%' OR "
SET @mySQL = @mySQL + "CONTAINS (CONTENT, '" + @keywordsContent + "')"
SET @mySQL = @mySQL + " UNION ALL "

SET @mySQL = @mySQL + "SELECT admin_id AS RecordID, business_name as TITLE, admin_id as ADMINID, content AS CONTENT, 'business.asp' AS URL FROM administrators"
SET @mySQL = @mySQL + " WHERE title like '%" + @keywords + "%' OR "
SET @mySQL = @mySQL + "CONTAINS (CONTENT, '" + @keywordsContent + "')"
SET @mySQL = @mySQL + " UNION ALL "

SET @mySQL = @mySQL + "SELECT menuitem_id AS RecordID, title as TITLE, admin_id as ADMINID, content AS CONTENT, 'business_subnav.asp' AS URL FROM main_menu_item"
SET @mySQL = @mySQL + " WHERE title like '%" + @keywords + "%' OR "
SET @mySQL = @mySQL + "CONTAINS (CONTENT, '" + @keywordsContent + "')"
SET @mySQL = @mySQL + " UNION ALL "

SET @mySQL = @mySQL + "SELECT subitem_id AS RecordID, title as TITLE, admin_id as ADMINID, content AS CONTENT, 'business_subsubnav.asp' AS URL FROM sub_menu_item"
SET @mySQL = @mySQL + " WHERE title like '%" + @keywords + "%' OR "
SET @mySQL = @mySQL + "CONTAINS (CONTENT, '" + @keywordsContent + "')"

-- PRINT @mySQL

EXEC(@mySQL)

I call it in ASP using the following:

sqltemp = "p_SEARCH_UNION_all @KeyWords = '" & Strkeywords & "', @KeyWordsContent = [""" & Strkeywords & """]"

Where StrKeywords is the user input from a form.

My problem is when the user enters an ignored words, I get the following error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The query contained only ignored words.

Is there any way to catch this in asp or SQL and prevent an error from appearing on the page?

Any help would be greatly appreciated..

Thanks,

Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources