CMD line compare quietly removes Full text indexing

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

CMD line compare quietly removes Full text indexing

Postby jchertudi » Thu Aug 22, 2013 7:23 am

SQL Compare Command Line V10.4.8.87
I created this view with a batch SQL file on many databases, copying and pasting the info out of the SQL Compare application

-- View

CREATE VIEW [dbo].[SV_V] WITH SCHEMABINDING
AS
SELECT sv.YearID, sv.ValueID, sv.ValueDesc
FROM dbo.Values_TBL sv
JOIN dbo.Years_TBL y ON y.bitActive=1 AND CONVERT(INT, y.YearID)=sv.YearID
GO
-- Indexes

CREATE UNIQUE CLUSTERED INDEX [CIX_ValueID] ON [dbo].[SV_V] ([ValueID])
GO
-- Full Text Information

CREATE FULLTEXT INDEX ON [dbo].[SV_V] KEY INDEX [CIX_ValueID] ON [Standards] WITH STOPLIST [EmptyStopList]
GO
ALTER FULLTEXT INDEX ON [dbo].[SV_V] ADD ([ValueDesc] LANGUAGE 1033)
GO

I run the SQL Compare application on the source and target DB, it says they are identical. Then I use SQL Compare CMD line to compare the database to update all of the other tables. After that CMD line compare completes, some of my full text indexes are removed!

{{ MISSING }}
CREATE FULLTEXT INDEX ON [dbo].[SV_V] KEY INDEX [CIX_ValueID] ON [Standards] WITH STOPLIST [EmptyStopList]
GO
ALTER FULLTEXT INDEX ON [dbo].[SV_V] ADD ([ValueDesc] LANGUAGE 1033)
GO

I have to compare a 2nd time either via CMD line or application to put them back.

I had used the batch SQL command to create these full text indexed views in the first place, since I have issues with updating views and adding full text indexing in one step. I had hoped this would avoid having to build the full text index twice. THe source database is under Source Control, possible an extended property is to blame?
jchertudi
 
Posts: 9
Joined: Thu Apr 30, 2009 4:02 pm

Postby jchertudi » Thu Aug 22, 2013 2:34 pm

May have answered my own question, there seems to be a silent error in the CMD line Compare. If I try to perform this same compare via the application, I get an error about the SCHEMABINDING on these Views. One of the tables in the VIEW is being altered, and the Compare from the application fails. The CMD line drops the views, changes the table, then recreates them, but doesn't add the full text indexing.
I've been told prior that MSSQL won't allow creation of a schema bound view and adding a full text index in one step, bummer.
jchertudi
 
Posts: 9
Joined: Thu Apr 30, 2009 4:02 pm

Postby Brian Donahue » Tue Aug 27, 2013 12:58 pm

Hi,

SQL Compare shouldn't have any "silent" errors unless you explicitly set the option to turn transactions off. If there is an error in the middle of a run of modifications, it should roll the changes back entirely. However there is a separate issue with full-text indexes in that they can't be modified inside of a transaction, so indexes need to be created either before or after the update, but definitely not inside the transactional framework in which the objects are being updated.

I am guessing that the changes rolled back, but some of the full-text modifications may have got left behind.

SQL Compare UI and command-line are based on exactly the same code, so should work identically if the same options are employed.

If you want us to look into it further, we'd be happy to if you have a whole script that will reproduce the issue.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Fri Sep 27, 2013 12:18 pm

Outcome so far is it looks like a Microsoft bug. SQL Compare is making a script that runs in SSMS but it's having a problem when run directly through SQL Compare.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest