Red Gate forums :: View topic - CMD line compare quietly removes Full text indexing
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

CMD line compare quietly removes Full text indexing

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jchertudi



Joined: 30 Apr 2009
Posts: 9

PostPosted: Thu Aug 22, 2013 7:23 am    Post subject: CMD line compare quietly removes Full text indexing Reply with quote

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?
Back to top
View user's profile Send private message
jchertudi



Joined: 30 Apr 2009
Posts: 9

PostPosted: Thu Aug 22, 2013 2:34 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Tue Aug 27, 2013 12:58 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Fri Sep 27, 2013 12:18 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group