SQL Compare Command Line Inconsistent (or I've done it wrong)

Pete6627

Hi all, so all I'm trying to accomplish is to sync across any tables that only exist in the source, excluding any that start with "ZZ_" (these are prefixed development only tables) and "Sys" as these are SQL system tables (for things like database diagrams etc)

I've got a PowerShell script that I'm trying to do all of this in. I've tried using both a project file which is set up exactly as I require and which, when I run manually, selects only the new tables I need to deploy, but when this is run via command line, all tables including the ones which already exist but are different are being deployed, instead of just the missing tables.

As such, I decided to try and create a manual command line instead of using a project file.
This looks as follows, with the path to the SQLCompare exe being aliased as just SQLCompare.

SQLCompare  "/Server1:Server1" "/db1:Database1" "/Server2:Server2" "/db2:Database2" "/Include:Missing" "/Include:Table" "/Exclude:Different" "/Exclude:Additional" "/Exclude:table:[zz_]" "/Exclude:table:[sys]" "/Scriptfile:$($SchemaOutputPath)" "/Force" "/Options:oec,deo,nc,nt,nacm,f,dac,icn,idsn,ifg,if,ift,isi,ii,inwn,iscn,itst,iup,iw,iweo,iwn" "/Sync"

I've replaced the server names and such but the rest is exactly as it shows in my PowerShell script. Unfortunately, this one only deploys two of the three tables that it should and I've no idea why it's missing the third one? The third table is named "ItemInventoryTestCertMap" and shouldn't be excluded by my options so I'm at a complete loss.

Any help appreciated, especially if it means I can use the project files to simplify this.

Pete6627

Hey Alex,
I've given what you suggest a try but unfortunately, it's failing to exclude the "ZZ_" prefixed tables.

Now that I know it's a regular expression I've tried using the following and few other variations but it really doesn't seem to like any of the exclusions I tell it. I'm currently using: "/Exclude:Table:^sys.*|^Z{2}.*" Which is failing to exclude anything.

I've even tried adding ^Item.* to the query as well to see if it starts ignoring the "ItemInventoryTestCertMap" table again, but it's like SQL compare is completely ignoring the exclude command because the table always shows.

Edit:
I just tried one more thing and this appears to have worked:

"/Exclude:Table:sys.*|ZZ_.*"

For some reason having the ^ in the query is causing SQLCompare to ignore the exclude command.
Odd but at least this is working now, Thanks for your help.

Alex B

Hi @Pete6627

I think the problem with the command you have there is that the third part of the /Include or /Exclude switch is a regular expression and the way you have them is not matching what you want them to match.  This as written:

"/Exclude:table:[zz_]" "/Exclude:table:[sys]"

Will be excluding any tables that contain any of the characters within the square brackets (so in the case of your example, ItemInventoryTestCertMap contains both  "y" and "s" and that is likely why it is being excluded.

For what you've said you are trying to do, I think these would be the correct regular expressions on the switches

/Exclude:table:^zz_.*<br>/Exclude:table:^sys.*

That is, anything starting with (the ^ character) "zz_" or "sys", followed by anything (the .* bit, which is 0 or more of anything).

You could even combine them into

/Exclude:table:^zz_.*|^sys.*

As for why the project file works from the UI and not from the command line, it should only include the objects you have selected for deployment (checked the boxes for) when you saved the project file, so perhaps when you run it manually you only select the additional ones, but the file is saved as having all checked?

At any rate, I think the manual command without the project file should work for what you need with the change above.

Kind regards,
Alex

Pete6627

Thanks very much Alex, I'll give that a shot and see how it goes then.

Appreciate it.