Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Errors with Full Text Index when scripting a DB to 2005

Posted on 2011-02-11
6
Medium Priority
?
1,013 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 1000 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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

926 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