Problem with CONTAINS in SP

Brian Donahue

Hi Gregor,

I don't think it should matter what statements are in a stored
procedure. I do know that the current version of SQL Compare has a problem
with SPs over 40KB that has been fixed and will appear in a future release.
If you examine your SP and find it's larger than 40K, please email me
directly and I will send you the patch to fix the problem.

Regards,

Brian Donahue
Red Gate Technical Support

"gregor walter" <gw@madgeniuses.net> wrote in message
news:ttWvIzmoDHA.1500@server53...
> Hi,
>
> SQL Compare doesnt work, when there is a CONTAINS Statement in a SP.
> If there is a Fulltext indexed defined or not on that table, it always
stops
> with error.
>
> best
> Gregor
> mad geniuses GmbH
>
>

Brian Donahue

Hello,

Updates to Stored Procedures that use the CONTAINS() function will still fail even in SQL Compare 3.x. This happens because the full-text catalogs cannot be created inside transactions. They either need to be created before or after the transactions begin in the transact-SQL migretion script produced by SQL Compare, so the safest bet is to put them at the end, to make sure that all of the relevant tables and columns exist before creating the full-text indexes.

The problem is that anything being created inside the transaction that depends on the availability of a new full-text index will fail.

There is no way to fix this problem at the moment, but a fix may appear in the upcoming version 4 if SQL Server 2005 allows full-text indexes to be created inside transactions.

Brian Donahue

SQL Server 2005 still has problems with creating full text indexes on columns inside a transaction. Therefore, this is not fixed in SQL Compare version 4. Until SQL Server can support this, we will continue to script full-text objects after the schema script's transactions are committed.