Link to home
Start Free TrialLog in
Avatar of mcunn
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





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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial