Solved

FULLText Search A View Problems

Posted on 2010-08-23
1
647 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Converting Teradata SQL to Oracle SQL (exadata) 3 28
SQL FUll Text Not returning any results 2 15
Sql query 107 22
recover sqlserver db 8 45
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now