Red Gate forums :: View topic - SQLCompare.exe ignores project filters
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

SQLCompare.exe ignores project filters

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



Joined: 12 Jun 2012
Posts: 72
Location: Edinburgh

PostPosted: Wed Jul 17, 2013 9:45 pm    Post subject: SQLCompare.exe ignores project filters Reply with quote

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:

Code:

SQLCompare.exe /project:"deploy_geodb_schema_from_repo.scp"


The output looks like this:

Code:

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:

Code:

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



Joined: 12 Jun 2012
Posts: 72
Location: Edinburgh

PostPosted: Wed Jul 17, 2013 9:56 pm    Post subject: Reply with quote

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:

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:

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


The output looks like this:

Code:

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



Joined: 12 Jun 2012
Posts: 72
Location: Edinburgh

PostPosted: Fri Jul 19, 2013 6:54 pm    Post subject: Reply with quote

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:

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:

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



Joined: 23 Apr 2012
Posts: 205

PostPosted: Fri Jul 19, 2013 11:00 pm    Post subject: Reply with quote

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