Solved

Errors with Full Text Index when scripting a DB to 2005

Posted on 2011-02-11
6
1,001 Views
Last Modified: 2012-05-11
I have recently scripted out a SQL DB from 2008R2 in order to recreate it on SQL2005.  The DB scripted perfectly, but I receive the following error when I recreate it on 2005:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '90'.
Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'HONOR_BROKER_PRIORITY'.
Msg 7601, Level 16, State 2, Procedure cms_GetMicroMsgSearchResults, Line 0
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'MicroMessage_tbl' because it is not full-text indexed.
Msg 7601, Level 16, State 2, Procedure cms_MicroMessageSearchUser, Line 21
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.MicroMessage_tbl' because it is not full-text indexed.
Msg 7601, Level 16, State 2, Procedure cms_MicroMessageSearchAdmin, Line 20
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.MicroMessage_tbl' because it is not full-text indexed.

I understand that I need to have Full Text Index turned on in order for the table to be created.  My question is how do I turn on Full Text Index on a table that is not created yet.  What can I do to correct these errors.?
0
Comment
Question by:mrwarejr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
MohammedU earned 250 total points
ID: 34876152
Error is clear your table/indexed views are not configured for full text index...where as I believe the server 2008 have full text index configured for these tables/views.
Create the fulltext index and run the script...
or you can script the objects again and make sure you add the option to script the fulltext indexes too...

Note: When you do the scripting through SSMS, script fulltext option is false, you change it true and script...
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 34888720
Besides that keep in mind some functioanlity from SQL 2008 is not full backwards compatible to SQL 2005 - i.e. 'HONOR_BROKER_PRIORITY'

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/5066370b-e32e-4fbd-a65b-4f4c4b997c96
0
 

Author Comment

by:mrwarejr
ID: 34888827
How do I create the full text index on the tables of a DB that is not created yet?  That is what I am trying to figure out.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mrwarejr
ID: 34888906
I completed it with full text index set to true and received this


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What does this mean?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 34889051
Here are all steps you usually need to do on a SQL server box/db never FT enabled:

/*      Enable Database for full text */

if DATABASEPROPERTY(db_name(), 'IsFulltextEnabled') = 0
      EXEC sp_fulltext_database 'enable'
GO


/*       Add Unique index on KBAttachment */

ALTER TABLE dbo.KBAttachment ADD CONSTRAINT IX_KBAttachment_FT UNIQUE NONCLUSTERED  (AttachmentId) ON [PRIMARY]
GO
 
/*       Create Full text Catalog */
exec sp_fulltext_catalog 'KBFullText', 'create'    
GO
 
/*      Add table to full text catalog */

--Table KBAttachment
exec sp_fulltext_table  'KBAttachment',  'create',  'KBFullText', 'IX_KBAttachment_FT'
GO
exec sp_fulltext_column  'KBAttachment',  'Name',  'add'
GO
exec sp_fulltext_column  'KBAttachment',  'Description',  'add'
GO

/*      Activate Full Text index */
exec sp_fulltext_table  'KBAttachment',  'activate'
GO

/*      Populate catalog */
exec sp_fulltext_catalog  'KBFullText',  'start_full'
GO
0
 

Author Closing Comment

by:mrwarejr
ID: 34897780
Thanks for your help.  The DB scripted without errors so I am closing the question.  I have run into other problems but that is somehow related to my Ektron Site.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

630 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