Default constraints in scripting folder

Stephen Brown

When scripting a database to a script folder, the default constraints are scripted with doubled up parenthesis and causes SQL Compare to show the table has changed when compared to another database. For example, this:

...
[SomeID] [int] NOT NULL CONSTRAINT [DF_SomeTable_SomeID] DEFAULT (1),
...

Becomes:


...
[SomeID] [int] NOT NULL CONSTRAINT [DF_SomeTable_SomeID] DEFAULT ((1)),
...

We have been migrating from SQL Server 2000 to SQL Server 2005 and I am unsure of whether the script was created from a 2000 or 2005 db. The system updates the tables fine with or without the double parenthesis, but the main problem is that it shows them as differences.

Michelle T

When SQL Compare shows that a table has changed, and this appears to be caused by doubled brackets in a check constraint, there is always actually another difference, not shown by the SQL Differences Viewer for some reason, which is causing SQL Compare to show on the main grid that the table is difference.

If you send me (michelle.taylor@red-gate.com) a database snapshot containing the table, or if you can't do that just the script of the two tables in question, I can probably work out what the difference is and tell you. (There are a couple of bugs we're working on fixing which cause 'invisible' differences in tables in this manner.)

mdeylen@ra.com.au

Hi,

I have this problem in scripting changes from a SQL 2005 database to 2000.

Has there been a resolution to this issue?

Marc.

Michelle T

The issue where the highlighting picks up the difference in defaults isn't fixed and probably won't be for a while.

Do you have tables which are showing up different on the main area of the screen and showing only these differences in the difference viewer, or some other related problem?

mdeylen@ra.com.au

Hi,

the problem is when we script from a SQL 2005 database to a SQL 2000 database, the SQL 2005 defaults have two brackets for defaults around the value. The SQL 2000 script only has one.

This means that SQL Compare always reports differences when in fact there aren't any.

I know its a bit sad that we are still using SQL 2000, but I know alot of people who still are, quite a lot of ISP's still use SQL 2000.

Marc.

Michelle T

SQL Compare doesn't (or shouldn't) put a table in the 'different' group just on the basis of a default with different bracketing.

Usually when it appears to be doing so, what is actually happening is that there is some other difference on the table which isn't as obvious as the bracketing difference in the SQL Difference Viewer (or maybe isn't shown at all).

It is likely you have found a bug where your tables do have some kind of difference (other than the bracketing), and it's being picked up by the main engine, but it isn't being shown in the SQL Difference Viewer.

If you could take a careful look over your affected tables (or send me a snapshot of the database (to michelle.taylor@red-gate.com) so I can have a careful look over them) and find what the extra difference is - synchronizing just one table and having a look at the script produced might give some idea - we can probably fix it and display the actual difference.

(It is possible, but unlikely, that you have found a bug where SQL Compare is actually picking up different bracketing as a difference. If you have managed to get it to do that we would very much like a look at a snapshot of the affected database so we can fix the problem...)

bjones@kudzuinteractive

I would like to disagree with the last posting. I am currently evaluating SQL Compare 6.2.0.271, and have experienced the same problem described by the other posters. In fact, I have just reproduced it in a way that I believe your developers can reproduce.

Pick a database to do comparisons on. Export that database to a script folder. Make a direct comparison between the database that you just exported and the script folder (turn off all setings to ignore schema differences). In my case, the comparison, which should show no differences, shows differences. Those difference all appear to be in the formatting of the DEFAULT constraint on columns, where the difference is 2 pairs of parens instead of one.

Take that same database used in the above paragraph and export it to a snapshot. Make a direct comparison between the database that you just exported and the snapshot (turn off all setings to ignore schema differences). This time, the difference report will show no differences.

In addition, if I go to my scripts folder, I will see that the actual exported scripts do not have the 2 pairs of parens, but just one. So it appears that the engine that reads the files and prepares for the differencing is actually adding the extra pair of parens in. What might be going on here? Is there a way for a user to change how the engine is interpreting the scripted files?

I really like the product and hope that this can be fixed in future versions!!

Thanks!!

bjones

Michelle T

1) If you're using scripts, you'll need to turn on Ignore Whitespace - SQL Server adds arbitary bits and pices of whitespace to some text, which means that scripts will often show up as different to live databases if you don't ignore whitespace.

2) You can change how the engine interprets the scripts by picking a different SQL Server version from the dropdown in the Edit Project Configuration dialog box.

3) If 'ignore whitespace' doesn't solve the problem, if you can send me (michelle.taylor@red-gate.com) the snapshots of your databases I can probably find what the difference is and tell you (or discover that it is in fact us treating the brackets as differences, at which point we'll try to fix it).

I'm sorry for the dogmatic tone about how brackets aren't the problem, but we've had an awful lot of customers claiming that we're differing on brackets in defaults and when we look at their databases it's always some other difference that they haven't noticed (and often something that we've failed to highlight, which is our fault and we will try to fix).

We know that the highlighting of brackets is causing this problem and we have plans for a new version of the difference highlighter which will fix it, but we haven't made any progress on those plans because any more complicated comparison of the text would potentially make the Differences Panel cause SQL Compare to grind to a halt when it tries to display really enormous stored procedures / triggers / functions etc.

MartinD1

Thanks for the information. We also ran into this issue and, in case it's of any use to your developers, we identified three situations where the parenthesis were incorrectly highlighted as part of the difference:

* The table had a foreign key where "NOCHECK" was different between the two databases. * The table had a trigger that was different between the two databases. * The table had an nvarchar column -- immeditately before the column highlighted with different parentheses -- that was a different size in the two databases.

Codernator

If you use the Management Studio to script the offending default on the two databases, you will see that one has double-parens and one doesn't.

If you then use the Redgate Synchronization Wizard either direction, the two will correctly match following the operation. However, if you instead simply attempt to drop and recreate the default with the desired number of parens, it will have no affect. Re-scripting the default will show that the attempt was futile.

Obviously, there's some obscure SQL setting somewhere that the Synchronization Wizard is aware of and can fix. I have been trying to find this setting, so far to no avail.

I want to know how the Synch wizard does it, because it's not always feasible to blindly synch two databases, and the script generated by the synch wizard doesn't seem to have any clues within it.