none
Cannot use a CONTAINS or FREETEXT predicate on table...

    Question

  • I am still a "very" novice user...

    Using SQL Server Studio Express 2005 only to make very simple database updates to the SQL product file that my shopping cart uses for my web site.

    What I do is open the "dbo.bvc_Product" table from the live SQL web server and edit the products descriptions, sizes etc. but since I have over 3,000 products sometime I need to view and select only certain products containing certain words in their description.

    So I was trying this simple query:

    SELECT     ID, ProductName, ProductTypeID
    FROM         dbo.bvc_Product
    WHERE     CONTAINS(ProductName, ' "Salt" OR "Pepper" ')


    But I get this error:

    SQL Execution Error.

    Executed SQL statement: SELECT ID, ProductName, ProductTypeID FROM dbo.bvc_Product WHERE CONTAINS (ProductName, ' "Salt" OR "Pepper"')

    Error Source: .Net SqlClient Data Provider

    Error Message: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.bvc_Product' because it is not full-text indexed.

    --------------------------------

    Is the error saying that I need to "Index" the 'dbo.bvc_Product' table?

    If so, how do I do that (without damaging the table? I am working on the live server...)

    THANK YOU!

     


    MarcoM
    Monday, August 08, 2011 3:48 AM

Answers

  • Yes, this is something you may want to create yourself. This is not a very easy setup, but Books Online are very helpful on this topic.

    Start from this link

    http://technet.microsoft.com/en-us/library/ms142497.aspx - this is for SQL 2008 R2, pick up your SQL Server version instead.

    and follow the instructions carefully.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, August 08, 2011 4:32 PM
    Moderator
  • Hi MarcoM,

    Since you are using SQL Server 2005 Express, please make sure that you have installed full-text indexing. Please refer to following link which elaborated on how to check and install this feature along with detail tutorials on how to set up full-text search.

    http://www.intraspin.com/news/sql-server-2005-express-full-text-search-greyed-out.

    Hope this helps.


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by Naomi NModerator Wednesday, August 10, 2011 3:12 AM
    • Marked as answer by MarcoM0857 Wednesday, August 10, 2011 2:10 PM
    Wednesday, August 10, 2011 2:56 AM

All replies

  • Well, the error is crystal clear. You need to create a full-text index on that table first.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, August 08, 2011 4:12 AM
    Moderator
  • Naomi:

    Thank you for you reply.
    At the bottom of my question I was asking "how" do perform this full-text index.

    Is something that I can do throughout SQL Server Studio Express 2005 or it is something that my web hosting provider should do on their end?

    Yes, this may seem a silly question, but as mentioned, I use SQL Server Studio Express 2005 just to (carefully) edit minor issue in the  data on by product database... If this full-text index could be done from SQL Server Studio Express 2005, please tell me a step-by-step procedure so I will learn and do this myself.

    Your help would be GREATLY appreciated.

    Thank you!

    Marco


    MarcoM
    Monday, August 08, 2011 3:48 PM
  • Yes, this is something you may want to create yourself. This is not a very easy setup, but Books Online are very helpful on this topic.

    Start from this link

    http://technet.microsoft.com/en-us/library/ms142497.aspx - this is for SQL 2008 R2, pick up your SQL Server version instead.

    and follow the instructions carefully.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, August 08, 2011 4:32 PM
    Moderator
  • Hi MarcoM,

    Since you are using SQL Server 2005 Express, please make sure that you have installed full-text indexing. Please refer to following link which elaborated on how to check and install this feature along with detail tutorials on how to set up full-text search.

    http://www.intraspin.com/news/sql-server-2005-express-full-text-search-greyed-out.

    Hope this helps.


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by Naomi NModerator Wednesday, August 10, 2011 3:12 AM
    • Marked as answer by MarcoM0857 Wednesday, August 10, 2011 2:10 PM
    Wednesday, August 10, 2011 2:56 AM