2000/2005 check constraint scripting

suskno

SQLCompare is finding a mismatch on check constraints that are identical due to adding an extra set of parantheses to the check value for 2005. I have tried sync'ing in both directions, but it will not correct. This leads me to believe the problem lies in the code generation rather than the databases. Is there anyway to avoid this situation showing as a mismatch without ignoring the constraint altogether?
2000 shows ADD CONSTRAINT [chk_35428] CHECK (([bds_day_id] = 35428))

2005 shows ADD CONSTRAINT [chk_35428] CHECK (([bds_day_id]=(35428)))
GO

:?:

Brian Donahue

Hello,

Thanks for your post. I recall a problem in early versions of SQL Compare 5 caused by SQL Server 2005, because SQL Server 2005 was adding additional parenthesis to some object attributes that did not appear in the script that SQL Compare had produced. There was some work done with SQL Compare later on that tried to compensate for extra parenthesis when the databases were compared again after synchronization. You may want to update to SQL Compare 5.3. If you have this already, by all means please let us know.

spivey24

I just upgraded to 5.3, and can confirm this is still an issue. I checked connect.microsoft.com for the bug and Microsoft states they are not planning to fix this issue of adding the extra parenthesis on check constraints. So it would be nice to have SQL Compare ignore these differences.

JonathanWatts

Hi there,

SQL Compare actually carries out two types of comparisons on objects, a symantic comparison, the status of which is displayed in the top grid. This takes into account the extra brackets and knows that (1) and ((1)) are equal.

The SQL Differences Panel, however carries out a straight textual comparison of the objects, we do this for speed of displaying the differences and in 95% of cases textual differences are also symantic differences. But this does cause problems with 2000 vs 2005 bracketing styles, highlighting differences in objects that the top grid states are identical.

I have tried out your constraint test case and whilst the bracketing style is highlighed in the SQL Differences Panel as different, the table is reported as identical in the top grid. At the moment we consider this expected behaviour. This would suggest that it is not this constraint that is causing your table to be reported as different, are there other differences in the object? Permissions, extended properties for example.

However, if the only difference in your table is the 2000 vs 2005 bracketing then this is a bug, if it is possible for you to do so please can you email the full creation scripts for the tables to jonathan [dot] watts [at] red [dash] gate [dot] com

Hope this helps,

Jonathan

spivey24

Yes you are correct. In 5.3, they now compare as identical in the top panel if that is the only difference, but the lines show as different in the lower details panel. Good enough for me...