DEFAULT Constraint issue in version 6.2.0.271

IronMike

I have a nightly job that uses SQL Compare command line to compare script directories against databases. The job compares around 50 databases. Ever since I upgraded to version 6.2.0.271, the reports are erroneously marking tables as different when, in fact, they are the same. The difference it shows has to do with DEFAULT constraints. On the left side, it may show a column with DEFAULT ((0)), but the right side shows DEFAULT(0). It doesn't matter if I remove or add () from the table script - I still get the same result.

Can you please provide me with a download to the prior version of SQL Compare? Now that I have to manually examine 50 reports and edit 50 scripts, I am no longer able to keep up with my daily workload.

Thanks,

Mike

Michelle T

Which version would you like - 6.1 or 5? I'll PM you a link to the download.

The difference that it's picking up isn't actually the default constraint difference that the viewer is showing - the viewer always shows that difference between different versions of SQL Server, it's a bug we need to fix with it, but that 'difference' doesn't cause objects to be actually marked as different.

Could you tell us what SQL Compare tells you it's doing to them to make them equal when you try to run a synchronization of one of these tables? Or perhaps even supply us with backups/snapshots of the offending databases so we can see the difference for ourselves? (My email address is michelle.taylor@red-gate.com if you want to do this.) Hopefully we can fix whatever bug has suddenly started coming up with differences for you, so you can keep using the latest version.

IronMike

6.1 please.

Thanks,

Mike

IronMike

I've found what causes the problem. When I upgraded to 6.2, I also removed the "Ignore Statistics" setting in my batch file. If a table has 1 or more user-created statistics and you don't ignore statistics, SQL Compare reports the tables as being different, and shows an extra set of parentheses around some of the Default Constraint values.

If you need more information, please let me know.

Thanks,

Mike

Michelle T

In the files that you sent us (thank you), the problem also went away if you ignored filegroups - the filegroups being different in case between the two databases. We should have been ignoring that as everything was set to case-insensitive (which is why it wasn't showing up in the SQL Differences pane), and fixing that problem seems to solve the problem in those files.

We also found a problem with statistics in that SQL Server reports a filegroup for them (the table filegroup) and this was interacting with the above problem to cause SQL Compare to think that the statistics were different.

Fortunately it looks like you've found a workaround. The fixes for this problem will be in the next release of SQL Compare, but currently we're still actively working on the development branch they're fixed in, so we'd rather not hand out private builds unless you really need them.

vlad

I have the same problem. SQL compare reports differences, because of the parentheses in Default Constraints values. I tried to change the Ignore options, but the problem still occurs. Is there any workaround for the problem?

Thanks,
Vladimir

Michelle T

The workaround depends on what the underlying problem is - the symptoms you're reporting are what happens when SQL Compare finds a difference that it doesn't display.

Some of the known problems that are causing this: if your filegroups have different capitalisation in different databases, we still report a difference even though case sensitivity is off; statistics report themselves as on a filegroup but this is never actually used so we don't display it anywhere but we still compare on it.

You can try setting case sensitivity on (which should at least show you the difference if it's filegroup case differences), and setting 'ignore statistics' on (which will solve the problem if it's statistics filegroups).

If neither of these help, I'll look into it further (you might have found a different problem entirely).

scafrithuric

Has this been resolved? What version? Or is any more information available?

Thank you,
Karl

Michelle T

The filegroup case problem should be fixed in v7. If you're still having a problem with 'invisible differences' (i.e. differences in tables which only show up in the SQL Differences pane as differences in bracketing or don't show up at all), please give us more information so we can track down the cause of your problem.