AdeptSQL Diff most recent changes

Bug fixes

• IDENTITY Seed/increment parameters weren't loaded correctly for tables defined in named schemas. In SQL2000 and before, there were no named schemas but only owners; in SQL2005 an object can have distinct schema and owner. Trying to obtain IDENTITY seeds/increments for tables, the Diff incorrectly used owner names instead of schema names. Fixed.
• DataDiff: columns stuck in "digest" mode: sometimes when the last column of a record was a BLOB loaded in "digested" mode, the first column in the next record would also be loaded (incorrectly) as "digested", because an internal flag was not consistently reset. Fixed.
• Owner filtering didn't work: when you tried to get only objects from a particular schema, the Diff still loaded objects from other schemas as well. This functionality is now restored; however, please be aware that when only part of the metadata is loaded, some of the dependencies may be lost so the Diff may not be able to generate correct script in all situations. Any FOREIGN KEYs pointing from a loaded table to a filtered-out one will NOT be loaded.
• No messages on aborted scans: when a database scan was aborted for any reason, the message panel did not appear and so the user was left without any information about what exactly had happened (on the other hand, when there were warings but the comparison completed, the messages were displayed). Fixed.
• Insufficient permissions were not reported: the minimal database-level permissions required to perform the schema scan are SELECT + VIEW DEFINITION. For a SELECT-only login, most of the scan was performed correctly, but definitions of views/procedures/triggers/functions as well as any expressions in DEFAULT/CHECK constraints were left empty. The Diff did not recognize this as a problem and provided no diagnostics when it happened. Now the Diff logs an error message when you scan the database without the VIEW DEFINITION permission, then later when a DEFAULT or CHECK is being scripted, the Diff checks for empty expression, inserts '???' instead and logs another error.
• Out-of-screen popup windows: the Diff remembers size and position of popup windows such as the SQL editor or DataDiff. If the screen resolution changes, it might happen that these windows popup entirely outside the screen bounds. The result could be very confusing: you press a "show script" button, the main window loses focus, but no editor window becomes visible. This version validates the stored window positions against the actual screen size, not allowing more than half of the window to stick out.
• Switching off constraint scripting: the "Constraint placement" option allows to specify how each kind of constraints should be scripted: on the column level or on the table level or separately or not at all. These settings were correctly applied when scripting a new table, but when altering a table the only possible way to script constraints is by a separate ALTER statement, so synchro scripts the "constraint placement" setting was entirely disregarded. However, the "don't script" option must still be taken into account when synchronizing constraints. This functionality has been restored in this release.
• Side-by-side refresh after changing options: When you change some options affect scripting, the scripts in the side-by-side view didn't reflect the changes until you double-click on the schema tree to refresh the side-by-side view. Now any relevant changes in scripting option are immediately reflected in the side-by-side scripts.
• Unneeded "-- *Constraints**": when a table has constraints, the Diff would show the "-- *Constraints**" section divider in the side-by-side view even when all those constraints were scripted inside the table. Fixed.
• Link/unlink commands didn't work. This feature is now fully implemented, you can link/unlink columns as well as first-level objects (such as procedures or tables). All the associations are saved with the project when you use the "File/Save comparison" command and automatically applied when you later open the comparison. The Diff synchronizes associated objects by using an appropriate combination of 'sp_rename' and ALTER commands.
• DataDiff incorrectly handling query errors: DataDiff did not correctly handle possible errors reported by the server when reading table data. Such errors can routinely happen if the user specifies an invalid filter condition, so the Diff is supposed just to report the error, stop loading the data and cancel the data comparison. Instead, such errors caused a cascade of AVs. Fixed.
• Tab order on connection panel: controls on that page were not correctly ordered for navigation with the Tab key. This is fixed now.

Changes

• Filegroups in indexes and tables: Diff now handles a bit differently tables and indexes with non-default, non-matched filegroups. Consider the following example: database A has an extra filegroup FG and table T is placed into this filegroup. Database B doesn't have any additional filegroups and it also has table T which is placed in the PRIMARY group, but is otherwise identical to its counterpart in db A.

  In this situation, previous versions of Diff would ignore the filegroup difference in table T regardless of the "Ignore filegroups" option (in "Options/Comparison/Details to ignore"). That was by design and it made a kind of sense, because how could the Diff sync that without adding the group first? On the other hand, if there is an option to ignore or compare filegroups, the Diff must not simply ignore it.

  This version, in the situation described above, would mark or ignore filegroup differences depending on the "Ignore filegroups" option. If you try to sync an object which refers to a filegroup missing in the target database, the Diff would report a warning and generate a commented-out "/* ON [filegroup] */" clause.

• Keeping original filegroups: when a sync script needs to re-create a table or index in the target database, that table or index was in a non-default FILEGROUP and we compare with "Ignore filegroup" option, that table or index should be re-created in the same file group where it originally was. In this situation, previous versions would not include the filegroup clause at all, so the object would be re-created in PRIMARY of whichever filegroup happened to be the default one. Now the Diff correctly handles this.
• Ignored changes in named SCHEMAs: the option "[x] Don't script users/groups/roles..." on the "Options/Scripting/Schema level" page also used to control comparison of named schemas: with this option checked, changes in the named schemas did not result in the database tree's root node marked as changed. Which means schemas were only synchronized when you selected the "Named schemas" node or when there were tables depending on these schemas, but not when you synced the entire tree. Although that was by design, such behaviour was rather obscure. The current version compares named schemas regardless of that scripting option.
• CHECKs not paired: unnamed CHECK constraints for the same column were not associated with each other, even if similarity between them was obvious for a human eye. For example, 'CHECK(id > 0)' on one side and 'CHECK(id >= 0)' would not be paired, because the Diff didn't analyze the expressions. In this version, the Diff still can't fully analyze the expressions, but it does support one common situation: when on each side there is just one unnamed CHECK constraint referring a particular column, these CHECKs are paired. Note that although the improved pairing is good for viewing the changes, it has no effect on the generated synchro SQL, because whether the CHECKs are paired or not, the only way to sync them is to drop and re-create.

Known issues

• The Diff detects a difference between a data type defined by 'exec sp_addtype' and an identical type defined by "create type" (in SQL2005). The reason seems to be that the 'exec sp_addtype' additionally creates a REFERENCE permission to PUBLIC while the "create type" doesn't. This is a quirk of MS SQL Server rather than a problem in the Diff, although the Diff should eventually implement some logic to work around the issue. Meanwhile the workaround is to disable permission comparison.

Bug fixes

• SQL2005 syntax to DROP from SQL2000: when comparing a SQL2000 and a SQL2005 database and scripting for the SQL2000 one, the Diff incorrectly generated some DROP statements using SQL2005-specific syntax. In particular, it would try dropping indexes using the "DROP INDEX index_name ON table_name" syntax, not supported on SQL2000. The problem was caused by some scripting code checking the server version on the wrong side of the comparison. Fixed.
• UDT in CONVERT(...): When the Diff generates a script to re-create a table and re-insert data from the original one, it generates CONVERT(...) expressions for columns whose types have changed. When a new column is specified via a user-defined type, the Diff would use the UDT name in the CONVERT(). This was an error, because the server only allows built-in type names in the CONVERT(). In this version, the target type inside a CONVERT is always the actual (physical) data type.
• Recreating tables unnecessarily: The Diff generates code to re-create a changed table when it decides that some of the changed columns can not be synchronized simply by using some kind of "ALTER TABLE..." statement. However, any column of TEXT/NTEXT/BLOB type would trigger table re-creation, even if there is no changes in this particular column. Fixed.
• Missing owner name while re-creating table: when a table belonging to another user is being re-created, the Diff forgets to script the owner name in several places: (a) when renaming the primary key and (b) when accessing the temporary name that the original table was renamed to. Fixed.
• ON DELETE SET NULL|DEFAULT: Previous Diff versions only recognized the CASCADE action in foreign keys (as in SQL2000), but not the SET DEFAULT|SET NULL actions introduced in SQL2005. This update implements those actions as well.
• Fixed a minor memory leak reading the metadata.
• Option "[x] Recreate dependent Views..." in "Options/Scripting/Tables" also affects scripting of dependent procedures and function, which was not obvious. The wording has been changed to reflect this behaviour.