SQLCompare.exe ignores project filters
isme
Posts: 119
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:
The output looks like this:
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:
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 << 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:
In the project file they look like this:
<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>
Iain Elder, Skyscanner
Comments
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:
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':
The output looks like this:
I can't combine the /exclude switch with the /project switch at all.
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:
The output of SQLCompare.exe now shows that the schemas are identical:
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?
I was able to reproduce this behavior and have logged this bug under the internal reference of SC-6447.
Product Support
Red Gate Software