AdeptSQL Diff most recent changes

Bug fixes

• Some of the "Don't show me again" dialogs were not properly stored. As the result, the Diff was stuck in the mode where it re-loads the schemas after each script execution. Fixed.

Bug fixes

• Reading triggers crashed on SQL2000: the previous Build 101 added some new queries to read the trigger order, but incorrectly assumed that the relevant system view has been available since SQL 2000, where in fact it only appeared in SQL 2005. This build reads the trigger order correctly on both SQL2000 and SQL2005/2008.
• The WITH EXECUTE AS... in views: the MS SQL Server allows this clause in procs/funcs/triggers, but not in views. Since the Diff used common code for scripting views and other "code objects", in some rare situations it would incorrectly report EXEC AS differences in VIEWs, then script those VIEWs with the EXEC AS clause. Fixed.
• When editing a timeout field on the connection panel, the Diff popped up a "Not a valid number" message box every time you clear the field, which was not exactly an error, but was very annoying. Fixed.
• When synchronizing a VIEW that had INSTEAD OF trigger(s) in the target DB, and WITH CHECK OPTION but no triggers in the source DB, the triggers must be removed before the view can be changed to have WITH CHECK OPTION. Previous versions of Diff would (incorrectly) attempt to alter the view first. Fixed.
• Spaces in functions: when the Diff scripted a function such as "create function f(@a int)...", it always inserted a space between the function name and the parameter list (i.e. "function f (@a...)"). If the "Lexical comparison" for code was disabled, this caused the function to be reported as changed in the schema tree, although the SQL scripts displayed in the side-by-side view were identical (being reconstructed for the display, with the extra space on both sides). Fixed (the extra space is no longer inserted).
• Resolving name conflicts: suppose the "source" DB has a view called [Customers], where is the "target" one has the table [Customers] instead. In MS SQL, both objects are in the same namespace so their names would conflict. When you sync the entire schema, the Diff knows to drop the old object (a table, in this example) before a different object with the same name (a view) can be created. However, if you selected only Views to synchronize, the Diff would get confused. On one hand, it knew that the conflicting object (a table) must be dropped first, but on the other hand, the conflicting table was not selected for scripting, so the Diff (incorrectly) tried to re-create it afterwards, which failed due to the naming conflict. This version solves the problem by permanently dropping the conflicting object, unless it has a (renamed) pair in the source DB.
• DataDiff couldn't read TIME/DATETIME2/DATETIMEOFFSET columns, reporting warnings and displaying/scripting those columns as NULLs. Also the DATE type was (incorrectly) displayed with both date and time parts. Fixed.
• In the DataDiff, a BIT value was loaded as -1 instead of 1. The -1 value was both displayed in the data comparison grid and used in scripts. This was a rather harmless bug, as the SQL Server accepts any non-zero integer as the "true" value for a BIT column. Fixed.
• Extra parentheses in RULEs/DEFAULTs: spaces and/or '--' comments at the end of RULE/DEFAULT objects get stored into [syscomments]. The Diff incorrectly recognized this tail as part of the expression and enclosed it all into another level of parentheses, producing incorrect SQL. This version reads RULE/DEFAULT expressions token-by-token, removing any comments.

Features

• Comparing/scripting trigger order is made optional, can be configured from "Options/Comparison/Other details to ignore".
• Option to ignore IDENTITY seed: when a table containing an IDENTITY column has been already filled with some data, the initial value (aka "identity seed") doesn't affect anything. Therefore it might be preferrable to treat the difference in the identity seed as a minor one and not synchronize it. An option for ignoring this difference has been added to "Options/Scripting/Tables".
• Compression is optional: the difference in data compression for columns and indexes has been made optional. It can be controlled from "Scripting/Tables" page of the Options dialog.
• Using compatibility level: when scripting, the Diff checks the target SQL server version to decide what SQL can or can not be applied to that server. However, MS SQL Server allows to set a "compatibility level" for a specific database, so that it behaves as if being on an older server version. Before this release, the Diff did not check for the compatibility mode, so it could generate SQL valid for the target server in general, but not valid for the target database running in compatibility mode. This version reads the compatibility level while loading the schema and then uses it for scripting.
• NOCHECK option for constraints: sometimes there is no need for the server to re-check the entire table when a constraint is added (and especially when it is dropped and then re-created). Adding constraints with NOCHECK option can make the scripts run much faster on large databases. This option can now be configured in "Scripting/Tables/Constraints", separately for FOREIGN KEYs and for CHECK constraints. Three scripting modes are supported for the WITH NOCHECK clause: (a) it is not used (it's the default, as in previous versions); (b) only script it when re-creating a temporarily dropped constraint; (c) always script WITH NOCHECK. The "NOCHECK re-created constraints" mode is supposed to be safer than scripting all constraints with NOCHECK. However, both NOCHECK modes should be used only when necessary and reverted back to the "checked" scripting afterwards.
• sp_rename not to be used for procs: the Diff can be configured to associate with each other stored procs that have different names but identical code. The Diff then synchronizes them using 'sp_rename'. However, renaming of code objects in MS SQL doesn't work quite correctly: the server renames them in sys.objects, but leaves the old names in the stored code. The Diff now has an options to avoid renaming code objects, re-creating them instead. The option in the "Options/Scripting/Procs, etc".
• Use ';' as statement separator: MS SQL Server doesn't require explicit ';' separators between SQL statements, however the server documentation recommends using them. Now the Diff has an option to script the trailing ';' in SQL commands. This option can be found at "Options/Scripting/Formating". It is enabled by default, so if you prefer the old way, you'll have to turn the feature off. NOTE that regardless of the setting, the semicolon is never appended to any of the schema objects whose SQL code is stored at the server "as is": procedures, triggers, functions, views, DEFAULTs and RULEs.

Known problems

• Exception on a cancelled refresh: clicking the "Refresh schema" button and quickly cancelling the refresh sometimes causes an exception (not reproduced, not fixed).
• Open connections on interrupted data comparisons: when a long data comparison is interrupted by the user, the DataDiff sometimes leaved one of the database connections open, blocking access to the data from other applications (until the Diff is restarted). This situation has not been reproduced, and the leak has not been properly fixed. However, since the DataDiff now reads with NOLOCK, even a connection left open shouldn't prevent other applications from accessing the database.

Bug fixes

• Altering TIMESTAMP columns: when a column needs to be changed from nullable to NOT NULL and there is data in the target table, the Diff first generates an UPDATE statement to replace any NULLs in that column with a default value. However, for a TIMESTAMP column such an update is not needed nor even possible, as the column's value is always generated automatically by the server. Previous versions of Diff didn't check for this special case, producing incorrect SQL. Fixed: (a) this version never tries to update the data in TIMESTAMPs; (b) the type conversion table has been corrected to disable most conversions between TIMESTAMP and other types (the server allows conversion from TIMESTAMP only to binary/varbinary or to integer types, and it does not allow *any* conversions into TIMESTAMP); (c) differently named TIMESTAMP columns are automatically associated with each other (so the Diff would use renaming for them, whenever possible).
• Most columns are by default NULLable, so the Diff doesn't script NULL unless specifically told to do so. However, a TIMESTAMP column without explicitly specified nullability by default becomes NOT NULL. This is reasonable, because a nullable TIMESTAMP hardly makes any sense at all. Still, the generated script wasn't accurate. This version of Diff always explicitly scripts nullability for TIMESTAMP columns.
• Recognizing "sp_settriggerorder": previous versions of Diff ignored the "run first/run last" attribute of triggers. Now this attribute is loaded, compared and scripted as a part of the trigger definition.
• Matching INSTEAD OF triggers: since there can be only one "INSTEAD OF" trigger for each of the UPDATE/INSERT/DELETE operations, the Diff now assiciates such triggers with each other even if they have different names. For example, if there is a [View1] view in the left-hand DB with an "INSTEAD OF UPDATE" trigger [uV1], and the corresponding right-hand view has an equvalent trigger named [updTrigView1], the two triggers will be matched to each other and, whenever possible, synchronized using sp_rename.
• Keep unique INSTEAD OF: suppose there is, in both databases, a view with two 'INSTEAD OF' triggers [Tr1] and [Tr2]. Let's further suppose that in the left-hand DB, [Tr1] is responsible for UPDATE, INSERT and [Tr2] for DELETE, whereas in the other db [Tr1] handles UPDATE, DELETE and [Tr2] handles INSERT. In such situation the Diff would provide incorrect script in at least one direction, trying to add an INSTEAD OF action to one trigger, while the other trigger still holds the same action. This version resolves such dependencies by dropping the blocking trigger first.
• Non-blocking DataDiff read: the DataDiff connections left open (see below) had such a negative effect because the SELECT queries might be locking the data being read (see TRANSACTION ISOLATION LEVEL in MS SQL docs). Now the reading is done with explicit NOLOCK hint, which is supposed to prevent the blocking behavior even if a DataDiff connection is left open for some reason.
• Nested comments: nested "/*...*/" comments are supported by the SQL Server, but the Diff didn't recognize them. This would lead to errors loading code objects such as stored procedures. Nested comments are now both correctly parsed when reading the schema and correctly highlighted in the code editor.
• Losing Editor font settings: changes to the editor font name and size were not properly saved in the registry. Fixed: now setting the editor font from either "Options/Visuals/Text Fonts" or from "Tools/Edit Options" works exactly the same, the font in any open editor windows is immediately updated and the changes persist after restarting the Diff.
• The font selection dialog in "Visuals/Text Fonts" opened without any font initially selected. The correct behaviour is to have the current font selected by default. Fixed.

UI changes

• No more GO options: from the very early days of AdeptSQL Diff, there was an option to change the 'GO' delimiter or to script it with or without putting on a separate line. In the context of MS SQL Server, the batch delimiter is always 'GO' and it must always be on a separate line, so the settings do not make sense. The options to change the statement separator have been removed in this version.
• The script confirmation: following a complain by one of the customers, who managed to get their database borked by accidently applying to it some scripts from the Diff, there is now a very prominent confirmation dialog that pops up before any script is sent to the server. The dialog has the usual "[x] Don't ask me again" checkbox, so that you can easily get rid of this annoyance. The dialog may be re-enabled from "Option/Visuals/Supressed Dialogs".

The most serious / commonly reported bugs fixed

• In single-database mode, the Diff would cause an AV when scripting FILEGROUP clause for a table or index. This was caused by the Diff attempting to access the "opposite" schema, without checking first that it has any. Fixed.
• DATETIMEOFFSET Columns in DataDiff weren't properly supported and caused the data comparison to fail with "Unsupported variant conversion" error. Fixed.
• Using table types as function parameters caused the scan to fail with "Invalid typecast" error. Fixed.
• Preserving CLR data: when there was a change in CLR column type and the table contained data, the Diff attempted to preserve the data, but simply using CONVERT() to copy the data didn't always work. This version correctly scripts conversions between CLR types and various string, binary and BLOB types.
• CLR-defined columns in DataDiff were displayed as NULLs. Specifically, this made it impossible to compare the predefined GEOMETRY and GEOGRAPHY data types. In this version, the DataDiff reads, displays and scripts any CLR-defined types as either binary or string values.
• Missing defaults for new date/time types: there are situations when the Diff has to use some default value to initialize a for a column. For this purpose, the Diff maintains a list of default expessions for all supported data types. Unfortunately, previous versions crashed when trying to obtain a default value for one of the recently added data types (XML/DATE/TIME/DATETIME2/DATETIMEOFFSET). Fixed.

New features

• Spatial indexes were not recognized as such and incorrectly displayed and scripted as regular (relational) indexes. This version of Diff fully supports spatial indexes on SQL 2008 by loading, comparing, displaying, scripting them and correctly resolving dependencies for them.
• CLR values in DataDiff: all CLR data types predefined in SQL 2008 (GEOMETRY/GEOGRAPHY and HIERARCHYID), probably as well as most user-defined types, provide implicit conversion to/from a string representation. It is now possible to display/compare CLR columns in DataDiff in either binary or text form. To select one of these, a "[x] Compare in text format" checkbox has been added to DataDiff's column configuration dialog.
• Improved Options dialog: a lot of changes there. Some options added, others re-grouped in a more logical way or moved to their own pages. All side-by-side options are now there and not in a separate dialog as before. The specific changes to the Options dialog are described below.
• Configurable fonts: previous Diff versions had the ability to choose a text font for the SQL editor, but not for the schema tree or the side-by-side panel. Now you can either specify a single common font for all three of them, or you can choose the fonts individually. The new font selection functionality is on the "Options/Visuals/Text Fonts" page, as well as on the specific pages for the Side-by-Side and Schema Tree configuration.

Changes and improvements in the Options dialog

• Hiding filenames in side-by-side view: we usually want to avoid displaying irrelevant differences in the side-by-side view. For example, names of the compared databases are commonly different, so there is an option to mask this difference by displaying a "<dbname>" placeholder instead. File paths in ADD FILE / ADD FILEGROUP are also going to be different, so in this version we've added a similar option to mask file paths in the side-by-side view. The new option is in "Options/Scripting/Side-by-side scripting".
• "Schema Options" becomes "Options": the "Tools/Schema Option..." command in the main menu is renamed to "Tools/Options...", because the dialog is where all configurable options in the Diff will be eventually integrated. So far, the "Side-by-Side" options have been moved there (see below). The "Editor Options..." is still a separate command with its own dialog, though.
• Navigate options with Ctrl-Tab: Navigation between pages of the Options dialog has been made easier. You can now press Ctrl-Tab to move to the next page or Ctrl-Shift-Tab to the previous one. (This feature only works while the input focus is on the tree on option pages).
• Added Visuals/Side-by-side options page. Previous versions of AdeptSQL Diff displayed a separate options dialog for the side-by-side textual comparison panel. The textual comparison in AdeptSQL Diff has always been implemented using a 3-rd party component, and its "native' configuration dialog contained some parameters that were obscure, irrelevant or duplicating parameters defined elsewhere in the Diff. Now the set of options for the side-by-side panel has been greatly simplified and whatever remained (the visual styles) added into the main Options dialog.
• Side-by-side display styles: on the improved Side-by-Side options page, you can now specify separate background colors for each side of the display, as well as the display style for "missing" lines of scripts (those that are only present on one side of the comparison). The "missing" areas may be just left empty (in the default background color), or can be indicated by a hatched brush with or without a special background color.
• Added "Scripting/Side-by-side" options page. Previous versions of Diff contained several settings affecting the side-by-side scripts, scattered throughout several options pages. Now all these options have been moved to this new page, making them much easier to find. Some new options have been added as well. The options include: (a) "Hide DB name" (moved), (b) "Hide file paths" (new), (c) Use compatible syntax (new), (d) "Show section headers" (new).
• Filter on owner/schema wording changed: there is an option in the "Schema Scan/Selective Loading" to allow loading of only objects owned by a particular user. Starting from SQL 2005, when the notion of "named schemas" was introduced, the Diff actually filters objects by the schema they belong to, rather than the owning principal. The difference is often difficult to notice, because the server has implicit schemas for all "legacy" principals such as "dbo", but nevertheless it is possible to have an object in schema X owned by principal Y. In this version of Diff there is no changes in how the filtering is actually done (and it is done by user on SQL 7.0/SQL 2000 databases and by schema on SQL 2005/2008 ones). The wording in this option, however, has been changed to reflect the reality.
• Options moved to "Scripting/General logic" options page: checkboxes "Always script USE" and "Script IF NOT EXIST" have been moved there from "Options/Scripting/Schema Level".
• The script preview box in the "Scripting/General logic" options page has been extended to reflect all 3 options now displayed on that page (transactions, "USE database" and the conditional scripting).
• Index options moved: the "Details to ignore" options page contained two checkboxes affecting comparison and scripting of indexes: "Ignore FILLFACTOR" and "Ignore PAD_INDEX". These settings obviously belong to the "Indexes and Statistics" options page, so they have been moved there.
• Options to ignore changes in expressions have been moved from the "Details to ignore" page to the "Code comparison" page.

Minor changes in the UI

• "Principals" in the schema tree: Starting from SQL2005, various kinds of database users and roles are all referred as "principals". The Diff displays all these entities in the same branch of the schema tree that in previous versions was titled "Users". Now the node has been renamed to "Principals". Warning: if you access AdeptSQL Diff programmatically, make sure that any scripts that refer the "Users" collection by name are changed accordingly!
• The two "Synchronize" buttons on the toolbar of the DataDiff window have pop-up hints that incorrectly stated that the keyboard shorts for these commands were "Alt-Shift-Left|Right". In fact, the shortcuts have always been "Ctrl-Alt-Left|Right". The hint texts have been corrected.
• DataDiff Script commands renamed: there is a popup menu in the DataDiff which, among other things, has commands for generating "INSERT", "DELETE" and "UPDATE" scripts. In fact, the "UPDATE" command generates a data synchronization script which can include any required combination of INSERT/DELETE/UPDATE SQL statements, whereas the other two commands generate, respectively, strictly INSERT or DELETE scripts, based on data in only one of the tables. The names of these three menu commands were somewhat confusing and didn't clearly reflect the difference in their functionality, so in this version they were renamed and slightly re-arranged.
• Long data displayed in DataDiff: When DataDiff displayed binary data or long strings, it showed no more than the first 40 characters of it, regardless of the grid column width. It now displays up to 400 characters, which should be sufficient for most situations.
• Display GRANT CONNECTs: any user added by a database by is default granted the CONNECT permission. For that reason, previous versions of Diff didn't include the explicit GRANT CONNECT into scripts. In this version, the Diff still supresses these permissions in the output scripts, but displays all GRANT CONNECTs in the side-by-side view for the entire dataabse.
• No more 16-color buttons: The Diff had an option to use a set of "rougher" 16-color images for all its toolbar buttons, instead of the more modern and smooth graphics it normally displays. The option might have been useful years ago when some displays were still limited to the 16-color "system palette", but is hardly of any use now. This version finally removes the "button style" switch from the "Options/Display/Schema Tree" page. Although the setting is removed, the underlying functionality is still there, so in the unlikely case you have been using the 16-color buttons, you will still see them (to switch back to the regular button style you'd now have to un-install and re-install the Diff, or ask us how to change the setting directly in the registry).

Various bug fixes

• More "digestable" types in DD: previous versions of DataDiff enabled per-column configuration of the column size threshold for TEXT, NTEXT and IMAGE columns (see the help file re details of "digesting" long columns). However, DataDiff didn't recognize VARCHAR/NVARCHAR/VARBINARY(MAX), XML and .NET-defined columns as potentially containing long data. The new version handles all these types or long content in a consistent way.
• Side-by-side view - empty GO: When displaying an object with sub-objects (such as a table with indexes and extended properties) on both sides of the side-by-side view, the Diff adds section headers (like "-- **Indexes**") on both sides, even if on one side the section is empty. This is by design, because the section headers are required to synchronize lines of the side-by-side textual comparison. However, the Diff still generated the final 'GO' for the empty section, which it shouldn't have done. Fixed.
• Section for TRIGGERS in the side-by-side view: the section header (see above) for triggers (displayed with the table they belong to) was not scripted. Fixed.
• The server name comboboxes on the connection panel are supposed to fetch lists of available server names from certain location in the Registry where the MS SQL Client software stores the last connected servers. Unfortunately, the registry location where the Diff was looking hasn't been updated since SQL 2000. Both SQLNCLI (native client for SQL 2005) and SQLNCLI10 (client for SQL 2008) keep the recent server lists in different locations. This version of AdeptSQL Diff looks at those newer locations as well.
• When a comparison is started programmatically, the connection parameters of the compared databases were not displayed in the connection panel. Instead, you would see there the connection parameters left from the last "interactive" comparison. This had no adverse effect on the actual comparison, except being very confusing if you look at the connection screen while your Automation script is running. This is fixed now.
• Side-by-side view - no GO: sometimes the SQL for several sub-items (such as indexes) were be displayed in the side-by-side panel without empty lines or GO separators between them. This bug didn't affect the actual generated script, only the displayed text. Fixed.
• Black bottom in Side-by-side view: when the SQL text in the side-by-side comparison panel is longer than the panel so that and the scrollbar is enabled, scrolling all the way to the bottom revealed a black area (not correctly repainted). Fixed.
• CONNECT permission not synched: CONNECT is a database-level permission that is by default granted to each created principal (but of course it can be revoked). In some situations the Diff incorrectly assumed that GRANT/REVOKE CONNECT need not to be scripted. If a user had a CONNECT privilege in one database, but not in the other, the Diff detected the difference, but failed to synchronize it. Fixed.
• Another aspect of the same problem is that when a new principal is created on the target DB, permissions granted to that user are not copied. To copy the permissions, the databases need to be re-compared after adding the user. This should not be a problem, except that principals with REVOKE CONNECT in the source DB will be by default created with CONNECT privilege in the target DB (i.e. the REVOKE wasn't scripted), which was unsecure. The CONNECT is the only such implicit permission for principals, and now the Diff checks to make sure that any non-connected principals are copied to the target as non-connected.
• Table functions locked in transaction: Some synchro scripts that involve re-creation of table functions produce an error "Transaction deadlocked..." when scripted with transactioning. in this version, CREATE FUNCTION is always scripted outside of a transaction.
• In SQL2005+, all built-in "users" and "roles" (such as [sys] or [INFORMATION_SCHEMA]) are combinations of a principal and a schema. The Diff reflects it by displaying something like "User + schema" after names of principals in the schema tree. However, previous versions didn't recognize the associated schema for some of the built-in principals, so that, for example, INFORMATION_SCHEMA was just displayed as 'User'. This error didn't actually do any harm, but it was untidy. Fixed.
• Column reverts to physical type: When a column in two tables uses the same UDT and appears identical, but the UDT definitions differ, the Diff had to change the UDT column to its underlying physical type before the UDT definition can be synchronized, but forgot to change the column back to its UDT afterwards. Fixed.

Documentation

• The help file was out of sync with the actual user interface of the Diff, especially in the descriptions of various options. The help file has been reviewed and updated.