Sync script order when full-text is involved

jerryhung

WHAT
The sync script fails when there is full-text stored procedure being created when the full-text catalog has not been created

Right now full-text gets added to the BOTTOM of the sync script, therefore I get the error from StoredProc says there's no full-text to use


HOW I FIXED IT
I manually do 2 diffs, 1st one only updates & creates the full-text catalog
2nd diff does everything else

ALTER TABLE [Business] ADD
[fulltext] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
PRINT N'Adding full text indexing to columns'
GO
ALTER FULLTEXT INDEX ON [Business] 
ADD (fulltext LANGUAGE 1033)
GO
.......... add the usual sync script below here .... minus Full-Text part

SUGGESTION
Maybe SQL Compare 7 can be smart enough to detect, IF there's full-text to be created, run it first (in its own separate batch as it cannot be in a Transaction), and everything else as normal

This is probably a rare case, but nevertheless I'd mention it

Thanks, you guys are great!

Michelle T

The problem we have here is that if the full text operations are at the top of the script, and the script then fails for any reason, the full text operations can't be rolled back (because full text operations can't be done transactionally).

I've added this forum post to the feature request to find a workaround for this, but everything we've thought of so far comes with a high risk of a script failing and not being able to roll back previous changes (for instance, if you put the stored procedure at the end and it fails to create - which is not uncommon as stored procedures can easily end up with no longer valid syntax in them - none of the previous changes can be rolled back).

jerryhung

I can see the difficult associated with this
thankfully I'm all done with full-text for now, no need to sync anymore

I'd just say make it an WARNING to warn the user that he/she may encounter this error, and possibly have to split the sync script manually (or by SQL Compare) into full-text, and non-full-text

Thanks for the reply Michelle