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_GetMicroMsgSearchResul
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
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_MicroMessageSearchAdmi
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.?
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_GetMicroMsgSearchResul
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
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_MicroMessageSearchAdmi
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.?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
What does this mean?
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()
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
/* Enable Database for full text */
if DATABASEPROPERTY(db_name()
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
ASKER
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.
ASKER