mcunn
asked on
FULLText Search A View Problems
OK, so I have view set up in SQL Server 2008 R2:
The tables are: (NO I DID NOT NAME THEM)
Recipe - Main Recipe Data
Cat_To-Recipe - Lookup Table
Recipe-Cat - Category Table
My View Looks Like:
SELECT R.ID AS Recipe_ID, R.Name + ' ' + R.Author + ' ' + CAST(R.Ingredients AS varchar(MAX)) + ' ' + CAST(R.Instructions AS VARCHAR(MAX)) +
((SELECT CAST(RC.CAT_TYPE AS varchar(max)) + ', '
FROM Cat_To_Recipe AS CTR LEFT OUTER JOIN
RECIPE_CAT AS RC ON CTR.Cat_ID = RC.CAT_ID
WHERE R.ID = CTR.Recipe_ID
ORDER BY RC.CAT_ID FOR XML PATH(''))) AS FullSearch
FROM dbo.Recipes R
Which returns
Recipe_ID | FullSearch
1 | Cat1, Cat2, Cat3
2 | Cat1, Cat3, Cat 7
Now I want to Index the view for fulltext searches.I Right click the view in management studio and select: FullText Index --Define Full Text Index which brings up the wizard.
However the Unique index is greyed out and I get the error: A unique column must be defined on the table/view.
I've goggled the error but couldnt find a solution that made sense.
Can Anyone help?
THanks!
Michael
The tables are: (NO I DID NOT NAME THEM)
Recipe - Main Recipe Data
Cat_To-Recipe - Lookup Table
Recipe-Cat - Category Table
My View Looks Like:
SELECT R.ID AS Recipe_ID, R.Name + ' ' + R.Author + ' ' + CAST(R.Ingredients AS varchar(MAX)) + ' ' + CAST(R.Instructions AS VARCHAR(MAX)) +
((SELECT CAST(RC.CAT_TYPE AS varchar(max)) + ', '
FROM Cat_To_Recipe AS CTR LEFT OUTER JOIN
RECIPE_CAT AS RC ON CTR.Cat_ID = RC.CAT_ID
WHERE R.ID = CTR.Recipe_ID
ORDER BY RC.CAT_ID FOR XML PATH(''))) AS FullSearch
FROM dbo.Recipes R
Which returns
Recipe_ID | FullSearch
1 | Cat1, Cat2, Cat3
2 | Cat1, Cat3, Cat 7
Now I want to Index the view for fulltext searches.I Right click the view in management studio and select: FullText Index --Define Full Text Index which brings up the wizard.
However the Unique index is greyed out and I get the error: A unique column must be defined on the table/view.
I've goggled the error but couldnt find a solution that made sense.
Can Anyone help?
THanks!
Michael
SELECT R.ID AS Recipe_ID, R.Name + ' ' + R.Author + ' ' + CAST(R.Ingredients AS varchar(MAX)) + ' ' + CAST(R.Instructions AS VARCHAR(MAX)) +
((SELECT CAST(RC.CAT_TYPE AS varchar(max)) + ' '
FROM Cat_To_Recipe AS CTR LEFT OUTER JOIN
RECIPE_CAT AS RC ON CTR.Cat_ID = RC.CAT_ID
WHERE R.ID = CTR.Recipe_ID
ORDER BY RC.CAT_ID FOR XML PATH(''))) AS FullSearch
FROM dbo.Recipes R
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.