SQLCompare.exe ignores project filters

Compares and synchronizes SQL Server databases, backups and scripts.

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

SQLCompare.exe ignores project filters

Postby isme » Wed Jul 17, 2013 9:45 pm

Summary

I have a SQL Compare project file that I can use to deploy my GeoDB database schema from SVN to a target database.

I want to use the file to deploy to several databases. I believe using SQLCompare.exe at the command line is the easiest way to do this repeatably.

When I use the project file at the command line I see a different result from when I use it in the GUI.

It looks like the command line version is ignoring the filters I defined in my project.

How do I make SQLCompare.exe respect the filters in the project?

My SQL Compare version is 10.4.8.87.

Example

I have already used the project in the GUI to sync a target database.

Now the GUI looks like this:

Image

The GUI says \"All objects identical\" and that 59 objects are \"excluded by filter\".

I use this command line to use SQLCompare.exe to repeat the comparison:

Code: Select all
SQLCompare.exe /project:\"deploy_geodb_schema_from_repo.scp\"


The output looks like this:

Code: Select all
SQL Compare: activated, edition: professional, serial number: REDACTED
SQL Compare Command Line V10.4.8.87
==============================================================================================
Copyright © Red Gate Software Ltd 1999-2013

Registering data sources
Creating mappings
Comparing
Summarizing Project Selections
Retrieving migration scripts
Checking for identical databases

Summary Information
===============================================================================================
DB1 =
DB2 = LOGSERVER.GeoDB

Object type             Name                                                           DB1 DB2
-----------------------------------------------------------------------------------------------
Schema                  offline_REDACTED                                                   <<
Schema                  offline_REDACTED                                                   <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSdel_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSins_REDACTED]                                          <<
StoredProcedure         [dbo].[sp_MSupd_REDACTED]                                          <<
-----------------------------------------------------------------------------------------------


There are 59 objects with differences in the output - the same number of objects excluded by the filter in the GUI.

There are two filters defined in the project. One excludes objects whose names begin 'sp_MS' (replication management objects). Another excludes schemas whose names begin 'offline_' (ephemeral schemas to support replication).

In the GUI they look like this:

Image

Image

In the project file they look like this:

Code: Select all
      <None version=\"1\">
        <Include>False</Include>
        <Expression>(@NAME LIKE 'sp_MS%')</Expression>
      </None>

      <Schema version=\"1\">
        <Include>False</Include>
        <Expression>(@NAME LIKE 'offline_%')</Expression>
      </Schema>
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby isme » Wed Jul 17, 2013 9:56 pm

Filter File Workaround (failed)

I used the GUI to save the filters to a seperate file called excludereplication.scpf.

I passed the filter file and project file to SQLCompare.exe using a command line like this:

Code: Select all
SQLCompare.exe /project:\"deploy_geodb_schema_from_repo.scp\" /filter:excludereplication.scpf


The output was the same - 59 differences.

Exclude Switch Workaround (failed)

I added an exclude switch to the command line to exclude all objects whose names begin 'sp_MS':

Code: Select all
SQLCompare.exe /project:\"deploy_geodb_schema_from_repo.scp\" /exclude:object:sp_MS*


The output looks like this:

Code: Select all
SQL Compare: activated, edition: professional, serial number: REDACTED
SQL Compare Command Line V10.4.8.87
==============================================================================================
Copyright © Red Gate Software Ltd 1999-2013

Error: The /project switch cannot be used in conjunction with the /exclude switch.


I can't combine the /exclude switch with the /project switch at all.
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby isme » Fri Jul 19, 2013 6:54 pm

In the end I worked around the limitation by replacing the project file with lots of command-line switches and other tools.

I used TortoiseSVN's command-line svn client to export the database schema from the repository, and then used SQLCompare.exe to compare the script directory to the target database and exclude the unwanted objects.

The commands look like this:

Code: Select all
svn export http://svn.cloudcorp.com/database/trunk/GeoDB GeoDB
SQLCompare /scr1:GeoDB /server2:LOGSERVER /database2:GeoDB /exclude:All:sp_MS* /exclude:Schema:offline_*


The output of SQLCompare.exe now shows that the schemas are identical:

Code: Select all
SQL Compare: activated, edition: professional, serial number: REDACTED
SQL Compare Command Line V10.4.8.87
==============================================================================================
Copyright © Red Gate Software Ltd 1999-2013

Registering data sources
Creating mappings
Comparing
Applying Command Line Items
Retrieving migration scripts
Checking for identical databases
Error: The selected objects are identical or no objects have been selected in the comparison.


It's surprising and a little frustrating that SQLCompare.exe does not parse all the data in the project file. It means I can't use the project file to drive scripted deployments in my multi-server environment.

Is there a possibility that the behavior of the command-line version will be upgraded to match that of GUI version?
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby Manfred.Castro » Fri Jul 19, 2013 11:00 pm

Sorry you are experiencing this.
I was able to reproduce this behavior and have logged this bug under the internal reference of SC-6447.
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests