Command line and /Include:Identical and /Filter

Compares and synchronizes SQL Server databases, backups and scripts.

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

Command line and /Include:Identical and /Filter

Postby robermar2 » Tue Mar 27, 2012 9:29 pm

We are using the sql command line to help automate our database deployments.

In order to not get build failures when the schemas happen to be exact, we have to use the /Include:Idential switch for the command line.

However we also need to use a filter so the compare also only includes certain schemas and we also don't want to inlude users and roles in the compare.

We have a filter file saved from the sql compare user interface.

However, according to the error reported by sql compare command line and your documentation, you can't use /Identical and /Filter at the same time.

How else can we use a filter but also not have the command line report a failure when the databases happen to be exact at the time it is run?

I can't imagine that we are the only ones who have run into this.
robermar2
 
Posts: 5
Joined: Wed Mar 21, 2012 9:27 pm

Postby Brian Donahue » Wed Mar 28, 2012 9:13 am

Hello,

Actually, no there has been a lot of complaints about include:identical so in 10.1 there is an /assertidentical switch you can use. This will replace error code 63 with error code 0 so your EXEC tasks don't fail and you don't have identical items in the results.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Thanks

Postby robermar2 » Wed Mar 28, 2012 2:22 pm

Great, I will give that a try.

Thanks for the quick response.
robermar2
 
Posts: 5
Joined: Wed Mar 21, 2012 9:27 pm

Another error

Postby robermar2 » Wed Mar 28, 2012 3:23 pm

I tried using the /assertidentical switch so that the command line does not fail when the schemas are identical.

However, now I receive this error:

Error: The /assertidentical switch cannot be used in conjunction with the
/synchronize switch. Please use SQLCompare.exe /? or SQLCompare.exe for more information.

I am actually trying to deploy changes, if there are any, so I have to use /synchronize. I also save the delta script to a file using /sf.

I don't understand why I can't use the switches at the same time.

I want to use a filter. I want to actually have the changes committed to the database being compared. I don't want it to fail if the schemas happen to be identical.

Any help would be greatly appreciated.
robermar2
 
Posts: 5
Joined: Wed Mar 21, 2012 9:27 pm

Postby james.billings » Mon Apr 02, 2012 8:38 pm

Thanks for your further post. It appears you're correct- we've got an existing internal bug that the assertidentical also can't be used with the report option.

From what I can tell, the assertidentical switch is intended to do just that- test if the databases are identical. So the workaround right now would seem to be to perform the compare with just that switch, grab the error code, and if the two databases are identical (error code 0), exit the script. If they differ (exit code 79) then proceed to the filtered sync.

I'll add a link to this thread as it does seem a bit pointless comparing once just to establish whether it should then "really" compare and sync.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby Brian Donahue » Tue Apr 03, 2012 9:15 am

I guess the best thing for now would be to go back to the old batch job workaround...
Code: Select all
SQLCompare.exe /db1:...
IF %ERRORLEVEL% EQU 63 GOTO EQUAL
EXIT /B %ERRORLEVEL%
EQUAL:
EXIT /B 0
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

with powershell

Postby robermar2 » Tue Apr 03, 2012 8:05 pm

Have you guys tried executing the same kind of thing in a power shell script instead of a batch file in an attempt to "eat" error code 63?

I would think this would work fine, but I am running into some issues where the compare works, but any output I request does not.

I am passing parameters for the script file output, html report as well as trying to limit the scope of the compare using a filter file. When passing this directly to the command line, it works fine.

However, when trying to pass all of this through a power shell script, I receive no errors, the compare works, but it ignores my filter file, does not output the script file and it does not save the html report.

I am going to submit a support ticket and provide you the power shell script I am trying to run.
robermar2
 
Posts: 5
Joined: Wed Mar 21, 2012 9:27 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests

cron