Solved

Errors with Full Text Index when scripting a DB to 2005

Posted on 2011-02-11
6
994 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

696 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