• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1018
  • Last Modified:

Errors with Full Text Index when scripting a DB to 2005

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
mrwarejr
Asked:
mrwarejr
  • 3
  • 2
2 Solutions
 
MohammedUCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
mrwarejrAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mrwarejrAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
mrwarejrAuthor Commented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now