CMD line compare quietly removes Full text indexing
jchertudi
Posts: 13 Bronze 2
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?
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?
Comments
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.
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.