FREETEXTTABLE

Hi..

I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:

Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.

The stored procedure :

CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)

SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO

I called the stored procedure using the following(in the Query Analayzer) :

USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT

but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:

SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')

Could any one help and tell me how to solve this problem?

http://support.microsoft.com/?kbid=892924

HTH

|||

Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.

The Search tool is working fine now in my website:www.engineeringprojects.net.

|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||

Sorry for being too late...

The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.