Solved

FULLText Search A View Problems

Posted on 2010-08-23
1
651 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:mcunn
1 Comment
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 33510508
Unfortunately in order to build a full-text search it requires a unique index.  In order to do that you would have to create an indexed view.  There are also some fairly strict rules as to what views can be indexed and I am not even sure if your query qualifies. Read up on the syntax for CREATE VIEW and specifically SCHEMABINDING.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Table where row act as column 11 70
Want an individual results display div 8 44
Options for Linking SQL tables to Access 2013 9 40
Merge join vs exist 3 27
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question