SQLCompare.exe ignores project filters

isme · July 17, 2013 4:45PM

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:

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:

SQLCompare.exe /project:"deploy_geodb_schema_from_repo.scp"

The output looks like this:

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                                                   &lt;&lt;
Schema                  offline_REDACTED                                                   &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSdel_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSins_REDACTED&#93;                                          &lt;&lt;
StoredProcedure         &#91;dbo&#93;.&#91;sp_MSupd_REDACTED&#93;                                          &lt;&lt;
-----------------------------------------------------------------------------------------------

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:

In the project file they look like this:

      &lt;None version="1"&gt;
        &lt;Include&gt;False&lt;/Include&gt;
        &lt;Expression&gt;&#40;@NAME LIKE 'sp_MS%'&#41;&lt;/Expression&gt;
      &lt;/None&gt;

      &lt;Schema version="1"&gt;
        &lt;Include&gt;False&lt;/Include&gt;
        &lt;Expression&gt;&#40;@NAME LIKE 'offline_%'&#41;&lt;/Expression&gt;
      &lt;/Schema&gt;

isme · July 17, 2013 4:56PM

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:

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':

SQLCompare.exe /project:"deploy_geodb_schema_from_repo.scp" /exclude:object:sp_MS*

The output looks like this:

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 · July 19, 2013 1:54PM

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:

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:

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?

Manfred.Castro · July 19, 2013 6:00PM

Sorry you are experiencing this.
I was able to reproduce this behavior and have logged this bug under the internal reference of SC-6447.

SQLCompare.exe ignores project filters

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk