AdeptSQL Diff 1.97 Build 100

AdeptSQL Diff 1.97 Build 100 offers users a lightning fast and intuitive tool to visually compare and synchronize two MS SQL Server databases. The program scans both databases and displays the combined schema tree view, with all differences color-marked.

Navigating through the database schema tree, you can see SQL definitions for individual objects (tables, view, stored procedures, etc) compared side-by-side in the larger panel to the right of the schema tree.

You can produce difference scripts for any chosen part or for the whole schema. The schema matching engine generates effective update SQL for any schema difference you need to synchronize, trying to preserve the table data whenever possible.

The generated scripts are displayed in a separate debugger-style popup window where you can edit them, execute them either uninterrupted or step-by-step, set breakpoints, turn on transactions and rollback on errors.

Even if you have only one database to work with, the drag&drop scripting (you simply drag one or more schema items to any text editor supporting regular OLE drag&drop) together with very flexible scripting options makes AdeptSQL Diff a perfect tool to explore your database schema. Currently, version for MS SQL Server is available. Support for other SQL databases is planned.

Enhancements:

• 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: 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 "" placeholder instead.  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.