Options
Sync script order when full-text is involved
jerryhung
Posts: 138
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
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!
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!
Jerry Hung
DBA, MCITP
DBA, MCITP
Comments
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).
Redgate Software
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
DBA, MCITP