Solved

FULLText Search A View Problems

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

813 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

11 Experts available now in Live!

Get 1:1 Help Now