Solved

Errors with Full Text Index when scripting a DB to 2005

Posted on 2011-02-11
6
991 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
  • 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 39

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 39

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

810 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