Thread: full-text searches

I'm starting to play around with full-text searches and I'm getting the following error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

The full-text query parameter for Fulltext Query String is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The full-text query parameter for Fulltext Query String is not valid.

Source Error:


Line 52: End If
Line 53: Else
Line 54: Using m_P As XmlPackage2 = New XmlPackage2(PackageName, ThisCustomer, ThisCustomer.SkinID, String.Empty, RuntimeParams, String.Empty, True)
Line 55: Try
Line 56: If (Not m_P.AllowEngine) AndAlso Me.Page.Request.Url.AbsoluteUri.IndexOf("engine.as px") <> -1 Then


Source File: C:\Users\k.paul\Documents\Visual Studio 2008\Projects\AspDotNetStorefront\Web\XmlPackageCo ntrol.ascx.vb Line: 54

Stack Trace:


[SqlException (0x80131904): The full-text query parameter for Fulltext Query String is not valid.]
System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) +212
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) +245
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a() +58
System.Data.SqlClient.SqlDataReader.get_MetaData() +112
System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6281668
System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6282737
System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior, String method) +211
System.Data.SqlClient.SqlCommand.ExecuteReader() +117
AspDotNetStorefrontCore.XmlPackage2.GetSqlData(Str ing OnlyRunNamedQuery) in C:\Users\k.paul\Documents\Visual Studio 2008\Projects\AspDotNetStorefront\ASPDNSFCore\XmlP ackage2.vb:1074
AspDotNetStorefrontCore.XmlPackage2..ctor(String PackageName, Customer cust, Int32 SkinID, String UserQuery, String AdditionalRuntimeParms, String OnlyRunNamedQuery, Boolean UseExtensions) in C:\Users\k.paul\Documents\Visual Studio 2008\Projects\AspDotNetStorefront\ASPDNSFCore\XmlP ackage2.vb:323
AspDotNetStorefront.XmlPackageControl.Page_Load(Ob ject sender, EventArgs e) in C:\Users\k.paul\Documents\Visual Studio 2008\Projects\AspDotNetStorefront\Web\XmlPackageCo ntrol.ascx.vb:54
System.Web.Util.CalliHelper.EventArgFunctionCaller (IntPtr fp, Object o, Object t, EventArgs e) +25
System.Web.Util.CalliEventHandlerDelegateProxy.Cal lback(Object sender, EventArgs e) +42
System.Web.UI.Control.OnLoad(EventArgs e) +132
System.Web.UI.Control.LoadRecursive() +66
System.Web.UI.Control.LoadRecursive() +191
System.Web.UI.Control.LoadRecursive() +191
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2428




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.4927; ASP.NET Version:2.0.50727.4927

Which version of SQL Server are you using? SQL 2005 and SQL 2008 handle these a bit differently and FTI in SQL 2008 is not yet supported.

SQL 2008

any idea when there will be support for 2008?

Bummer about the support for SQL2008, though you can just do this manually in the stored procedures and setup FTS that way.
SQL2008 supports FTS and it's real easy to set up (and a good clip faster than 2K5), just have to pick your columns carefully.

Can you advise on how to do this mc9000?

SQL 2008 does support FTI no problem, I think the snag is that the built in FTI search of AspDotNetStoreFront is not currently compatible with SQL 2008.
We integrate our own custom full-text search on 2008 for clients on a weekly basis an have no issues. Along with this upgrade we also provide the sorting and filtering mechanisms to help make navigating the site a breeze.

It depends on what code you are using to point to the FTI, but here are a few steps you could try. If you get stuck, holler and we could discuss our solution.

In SSMS:
Expand your ASPDNSF Database
Expand Tables
Right click the Product table
Select Modify (or Design)
Right click Key icon next to ProductID
Select FullText Index..
Press Add
Click blank area next to Columns
Press ... button
Select Name Column
Press Ok
Check that Active is "Yes", Change Tracking Setting is "Automatic"
(note: It may take awhile for the full text index to crawl the product table, so if you don't get immediate results, check back
later)
Press Close
Save and Close the Product table

AspDotNetStorefront Full-Text Search(FTS) fully supports SQL2008. You should send in a ticket to support to get the patch.

Will do. Thank you.

Looks like SQL 2008 R2 breaks the search functionality.

I was able to fix it for my shop by researching a little...

http://social.msdn.microsoft.com/For...-61a17bccb8e3/

It seems that installing .NET framework 4 breaks the way full text search works, and means you have to match input text size to output text size.

So I looked through the failing stored procedure "[dbo].[aspdnsf_GetProducts]" and discovered the failing area was " [dbo].[KeyWordSearch] ". It was passing in a varchar(100), but expecting a nvarchar(4000). Running the following will fix it for you....

NOTE: I am not an aspdnsf employee, I'm just fixing a live bug on our site the only way I can.

--Backup our old function, incase we need to roll back
CREATE FUNCTION [dbo].[KeyWordSearch_BKP](@srchString varchar(1000)) RETURNS TABLE AS RETURN(SELECT productid FROM dbo.product WHERE contains(*, @srchString ))
Go
--Put our fix in
Alter FUNCTION [dbo].[KeyWordSearch](@srchString nvarchar(4000)) RETURNS TABLE AS RETURN(SELECT productid FROM dbo.product WHERE contains(*, @srchString ))


Worked for me!
http://www.hitched.co.uk/wedding-sto...archterm=heart

I've worked out a fix if you havn't got a patch yet...

http://social.msdn.microsoft.com/For...-61a17bccb8e3/

It seems that installing .NET framework 4 breaks the way full text search works, and means you have to match input text size to output text size.

So I looked through the failing stored procedure "[dbo].[aspdnsf_GetProducts]" and discovered the failing area was " [dbo].[KeyWordSearch] ". It was passing in a varchar(100), but expecting a nvarchar(4000). Running the following will fix it for you....

NOTE: I am not an aspdnsf employee, I'm just fixing a live bug on our site the only way I can.

--Backup our old function, incase we need to roll back
CREATE FUNCTION [dbo].[KeyWordSearch_BKP](@srchString varchar(1000)) RETURNS TABLE AS RETURN(SELECT productid FROM dbo.product WHERE contains(*, @srchString ))
Go
--Put our fix in
Alter FUNCTION [dbo].[KeyWordSearch](@srchString nvarchar(4000)) RETURNS TABLE AS RETURN(SELECT productid FROM dbo.product WHERE contains(*, @srchString ))


Worked for me!
http://www.hitched.co.uk/wedding-sto...archterm=heart

Chris,
I use your fulltext search and I'm wondering what needs to be changed to remove a column from the index.

So far, I have removed it via the database by right-clicking on the product table and then modifying which columns are indexed.

To confirm I did this correct, i ran a query directly against the database and it says that that column is not part of the index.

Then I modified the stored procedure in two places where it was searching using freetext.

However, when I try to search from the enduser side, it is giving an error that it is still trying to search on the column I deleted from the index.

Is there another place besides the stored procedure (vibe_getattributeproducts) that needs to be changed when you change the index?

THanks

Chris, you emailed me requesting which version - I run 8.0.1.2. Please let me know the property procedure for removing a column from your fulltext search. I know the stored procedure and the database itself needs modifying, but what else is there? Many Thanks!

Originally Posted by movingon

Chris,
I use your fulltext search and I'm wondering what needs to be changed to remove a column from the index.

So far, I have removed it via the database by right-clicking on the product table and then modifying which columns are indexed.

To confirm I did this correct, i ran a query directly against the database and it says that that column is not part of the index.

Then I modified the stored procedure in two places where it was searching using freetext.

However, when I try to search from the enduser side, it is giving an error that it is still trying to search on the column I deleted from the index.

Is there another place besides the stored procedure (vibe_getattributeproducts) that needs to be changed when you change the index?

THanks