Command line and /Include:Identical and /Filter

robermar2

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.

Brian Donahue

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.

robermar2

Great, I will give that a try.

Thanks for the quick response.

robermar2

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.

James B

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.

Brian Donahue

I guess the best thing for now would be to go back to the old batch job workaround...

SQLCompare.exe /db1:... 
IF %ERRORLEVEL% EQU 63 GOTO EQUAL 
EXIT /B %ERRORLEVEL% 
EQUAL: 
EXIT /B 0

robermar2

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.