SQL Compare 12 is showing an invalid comparison
cureo
Posts: 1
in SQL Compare
When attempting to do a comparison, sql compare started giving me bogus results. I flipped back to the old version and everything works fine. I confirmed that the tables it claims are missing are in fact present on both databases. It almost appears as if it is using some cached version ....
Tagged:
Comments
I think what might have happened here is you've done a deployment without 'Recompare after deployment' selected. Normally SQL Compare will re-read and re-compare the databases after a deployment completes, which should mean that you see updated comparison results. It might also be the case that you've done a deployment outside of SQL Compare, in which case it won't know to update the comparison results. The fix for both of these cases is to press the 'Refresh' button on the toolbar, which should cause Compare to throw away the older (cached) results.
If that doesn't work, it might be worth looking into the project options and filters you have set up in the project - if those are different between the two instances of Compare that might also be causing the different results that you're seeing.
If you still see differences between v11 and v12 with a fresh comparison and the same project options / filters in both cases, that would be a pretty serious bug, so we'd definitely investigate further.
I'm doing a comparison for an upcoming deployment.
DEV -> FINAL
And Compare 12.2 is saying that a stored procedure has been deleted from the source (dev) when it definitely has not been. I've run that stored procedure in DEV and recreated it and it's still coming up as 'removed' in the compare.
I've done another comparison by switching the compare to DEV -> SEED which are identical and the stored procedure is coming up fine in both databases.
Switch back and it's suddenly missing again from the source, even though I only changed the destination.
This is a major fault - please look into it immediately as it can have some serious consequences if someone doesn't pick it up.
Also another test I did - changed the source to a snapshot that I made a few mins ago - the missing proc is working fine in that.
DEV (snapshot) -> FINAL works.
DEV (live) -> FINAL doesn't.
DEV (live) -> DEV (snapshot) works - comparison is identical.
DB (Live) -> DB (Target) - missing proc
DB (Snap) -> DB (Target) - no missing proc
DB (Live) -> DB (Snapshot) - identical
Half my fault, half app's fault.
Issue was "case sensitive comparison" is turned on.
* When comparing with snapshot it incorrectly shows databases as identical and doesnt pick up that there is a case sensitive rename
* When comparing with a database it shows as an add / drop instead of an alter (rename)
So the proc was showing up as a new object (which I missed), and also showing up as a drop.
This also means if the "delete" option is not ticked, it'll try to create an already existing proc and the script will fail.
So looks like there needs to be some more handling around renaming procs with case sensitivity turned on.
So the 2 bug fixes are:
1) Snapshot -> DB is ignoring case sensitive renames of procs (and others?)
2) Case sensitive renames of procs (and others?) need to be shown as a modification in the interface, but scripted as a separate drop/create in the script.
SQL Compare's case sensitivity option treats differently cased objects as being totally unrelated, so it will drop and recreate (unless you use a rename feature such as table mappings to avoid doing this).
Incidentally, SQL Compare 12.2.3 includes a change to the way we register databases from snapshots: where the case sensitivity used to be baked into the snapshot, SQL Compare now respects the project's case sensitivity option even if it has been changed since the snapshot was created.
Cheers,
Sam.
Redgate Software
As I have mentioned in support request - I believe this approach is incorrect.
If you tick both add/delete then everything works fine.
If you tick add (and not delete - some people ignore deletes as they are destructive), then the resulting script will crash and return an "already exists" error.
Renaming the case (name) of an object is a single operation.
In theory, I should be able to click all new, all changed, no deletes and have the script work without failure.
The add can't ever work without the delete being run first (it will always do CREATE and not ALTER) - so they are one in the same option.
(Sorry for hijacking the original meaning of thread)
Another possible solution would be to use the target database's collation to make inferences about what objects can coexist, but this leads to edge cases when working with scripts folders, source control etc. where the collation is not necessarily known, let alone correctly specified.
You might also be interested in the "existence checks" option, which will skip the deployment of objects that already exist. This will allow your script to run without error, although it won't deploy the new function since the existence check will detect the old one.
Redgate Software
I think the existence check is the incorrect approach, I think that's possibly even worse than a script crash. Assuming the case of the proc was changed, and also some important code was changed in the content - if the user chose to deploy that script by 'ticking add' and the existence check was enabled, the script would pass and they would assume the code has been deployed when it hasn't been. At least with a crash, you know there is an issue to take action.
In terms of the collation I think that's ok. Assuming that most (if not all) cases two objects with the same name is assumed to be the same object, including with scripts. Unless there is a specific collation to forbid that, I think that's ok to treat it as a rename by default.
If you're worried about that then perhaps create a user option telling the product how to treat case-differing objects - with the default being a rename. On the balance of probabilities I'd say less than 1% of cases would require 2 independent objects of differing cases.