error synchronizing db with full-text index to an empty db

Brian Donahue

Hi Tom,

Thanks for writing. We've had a similar problem reported last week from
another user. The scripting engine doesn't seem to check to see if stored
procedures depend on full-text indexes before they script the changes to the
destination database. Hopefully this can be fixed soon, now that we have an
example to work with.

Kind Regards,

Brian Donahue
Red Gate Technical Support

"Tom McGrath" <tom.mcgrath@zeddcomm.com> wrote in message
news:gIPIi4M9DHA.2768@server53...
> I have a database with full-text indexing enabled and a site search stored
> procedure that uses the full-text index. I am trying to synchronize this
> with a new, empty database. The process fails with the following message;
>
> Cannot us a CONTAINS or FREETEXT predicate on table 'pages' because it is
> not full-text indexed.
>
> If I exclude the offending stored procedure, I fail on the synchronization
> of the full-text index itself with the following message;
>
> An invalid parameter or option was specified for procedure
> 'sp_fulltext_column'.
>
> The script generated by SQL Compare is:
>
> /*
> Script created by SQL Compare from Red Gate Software Ltd at 2/16/2004
> 4:55:46 PM
> Run this script on (local).hawk_content to make it the same as
> SNOWBALL.hawk_content
> Please back up your database before running this script
> */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
> GO
> CREATE TABLE #tmpErrors (Error int)
> GO
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> GO
> ALTER TABLE [dbo].[html] DROP CONSTRAINT [DF_html_binaryHTML]
> GO
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
> GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
> TRANSACTION END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ARITHABORT ON
> GO
> ALTER TABLE [dbo].[html] ADD CONSTRAINT [DF_html_binaryHTML] DEFAULT
> (0xffffffff) FOR [binaryHTML]
> GO
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
> GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
> TRANSACTION END
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
> GO
@TRANCOUNT&gt;0 BEGIN
> PRINT 'The database updated succeeded'
> COMMIT TRANSACTION
> END
> ELSE PRINT 'The database update failed'
> GO
> DROP TABLE #tmpErrors
> GO
> sp_fulltext_catalog N'pages', 'create'
> GO
> sp_fulltext_catalog N'html', 'create'
> GO
> sp_fulltext_catalog N'images', 'create'
> GO
> sp_fulltext_catalog N'photoGallery', 'create'
> GO
> sp_fulltext_catalog N'products', 'create'
> GO
> sp_fulltext_table N'[dbo].[html]', 'create', N'html', N'PK_html'
> GO
> sp_fulltext_table N'[dbo].[images]', 'create', N'images', N'PK_images'
> GO
> sp_fulltext_table N'[dbo].[pages]', 'create', N'pages', N'PK_pages'
> GO
> sp_fulltext_table N'[dbo].[photoGallery]', 'create', N'photoGallery',
> N'PK_photo_gallery'
> GO
> sp_fulltext_table N'[dbo].[products]', 'create', N'products',
N'PK_products'
> GO
> sp_fulltext_column N'[dbo].[html]', N'binaryHTML', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[images]', N'alt', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[pages]', N'pageName', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[pages]', N'meta_keywords', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[pages]', N'meta_description', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[photoGallery]', N'imageTitle', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[photoGallery]', N'imageDesc', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[products]', N'productName', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[products]', N'styleNumber', 'add', 1033
> GO
> sp_fulltext_column N'[dbo].[products]', N'description', 'add', 1033
> GO
> sp_fulltext_table N'[dbo].[html]', 'activate'
> GO
> sp_fulltext_table N'[dbo].[images]', 'activate'
> GO
> sp_fulltext_table N'[dbo].[pages]', 'activate'
> GO
> sp_fulltext_table N'[dbo].[photoGallery]', 'activate'
> GO
> sp_fulltext_table N'[dbo].[products]', 'activate'
> GO
>
>

Brian Donahue

There are still issues with full-text indexes and SQL Compare because SQL Server 7 and 2000 do not allow us to script full-text indexes inside transactions. That only leaves us with one option: to put the full-text indexes at the end of the script, after the transactional portion has completed.

This always causes problems with queries that use CONTAINS() and other full-text features inside of views and stored procedures, because the full text index hadn't yet been created and can't be created until the end.

Hopefully SQL 2005 will let us script FTX inside of transactions. If it does, we'll look at fixing this, at least for SQL 2005 users!