Softpedia >Windows >Internet >Servers >Database Utils >AdeptSQL Diff >  Changelog

AdeptSQL Diff Changelog

What's new in AdeptSQL Diff 1.98 Build 104

Sep 20, 2010
  • Bug fixes:
  • Exception comparing table data: in the previous update (Build 103), the DataDiff data loader was changed to read the "true" value from a BIT column as 1, rather than -1. Unfortunately the change caused an exception when the BIT column contained NULLs. The problem is fixed now in Build 104: both NULL and non-null BIT values are loaded correctly (as NULL, 0 or 1).
  • Cutting/Pasting/Copying DataDiff scripts: with the SQL editor window opened from the DataDiff window, the Ctrl-C/V/X shortcuts were processed, incorrectly, by the data grid rather than the SQL editor window (even though it had the keyboard focus). This is fixed in Build 104.
  • Restored "Smart comparison" for expressions in DEFAULT/CHECK constraints: SQL Servers 2005/2008 have a silly habit of enclosing these expressions into an extra pair of parentheses, so that a "DEFAULT (0)" comes up as "DEFAULT ((0))". The older Server 2000 leaves such expressions unchanged, and this would cause false positives when comparing SQL2000 databases against SQL2005/8 ones. The "smart comparison" option in the Diff was introduced a long time ago to take care of this and other similar "false positives" in SQL expressions. In one of the previous updates the Diff stopped "peeling" the outer parentheses as a part of some other change, but that also prevented the "smart comparison" from detecting/ignoring the situation. Fixed.
  • Trial mode was limited by 30 starts: in the trial mode, the Diff displayed "Starts remaining: N out of 50", but actually counted down from 30, rather then 50. Fixed.
  • The Diff losing focus: occasionally, when any of Diff's popup windows (such as an SQL editor window) was being closed, Diff's main window would lose focus and hide behind other windows. Fixed.
  • Minor changes:
  • F10 for "Step" in VS-style keymap: in the Diff, keyboard shortcuts for script execution can be switched between "Delphi style" and "Visual Studio style" layouts (see the "Tools/Select a keymap" menu). The F8 key was used for the "Step" command in both layouts, although MS Visual Studio uses F10 for the equivalent command ("step over"). The shortcut has been changed to F10. The shortcut for "skip over a statement" command are changed to Ctrl-Shift-F10 or Ctrl-Shift-F8 in, correspondingly, VS or Delphi modes (i.e. Ctrl+Shift+[the step hotkey]).

New in AdeptSQL Diff 1.98 Build 103 (Jul 21, 2010)

  • 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.

New in AdeptSQL Diff 1.98 Build 102 (Jul 9, 2010)

  • Bug fixes:
  • Reading triggers crashed on SQL2000: the previous Build 101 added some new queries to read 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".
  • 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.

New in AdeptSQL Diff 1.97 Build 101 (Mar 26, 2010)

  • This update includes improved comparison/scripting of TRIGGERs and scripting of TIMESTAMP columns, as well as some minor corrections.
  • 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": previos 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".

New in AdeptSQL Diff 1.97 Build 100 (Dec 29, 2009)

  • 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 "" 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.

New in AdeptSQL Diff 1.96 Build 98 (Sep 16, 2009)

  • New features:
  • Table types: support for user-defined table types (in SQL Server 2008) is added in this version. All table types are loaded, compared and scripted, dependencies correctly resolved.
  • IF NOT EXISTS for all objects: the conditional scripting logic is now implemented not only for tables, but for for all code objects (procedures, functions, triggers, views), database principals, named schemas and user-defined types. Please note that, as before, the IF NOT EXISTS logic is only included into single-database "CREATE" scripts, but not into any synchronization scripts (which are supposedly applied to the target database with a known schema).
  • Accordingly, the option controlling the "IF NOT EXISTS" scripting has been moved from the "Options/Scripting/Tables" to the "Options/Scripting/General Logic" tab of the Options dialog. The options for transaction scripting are now on that same tab as well.
  • Case-sensitive name comparison: although names in SQL Server are usually not case-sensitive, it has always been possible to force case-sensitive comparisons in the Diff. However, with case-sensitive comparison names that only differ in character case were considered different and unrelated. Accordingly, they would be synchronized by DROP/CREATE (rather than by renaming) and, for tables, no data would be preserved. This was not exactly a bug, but this version takes a more logical approach: it would associate these case-changed objects with each other, show the name as "changed" and synchronize by renaming, whenever possible.
  • "Smart" comparison for SYNONYMs: a new option has been added to normalize the "real" names behind synonyms before comparing them. If a synonym refers to an object residing on the same server, database or schema, the corresponding parts will be removed from the multi-part name before the comparison. It means that if, for example, you have a synonym X in [db1].[sch1] referring to "[db1].[sch1].[Table1]" and it is compared against a synonym X in [db1].[sch2] referring to "[sch2].[Table1]", the two will be found identical. The option is on the new "Comparison/Synonyms" tab of the Options dialog.
  • Bug fixes:
  • Error loading XML indexes in SQL 2008: in some situations, the Diff was unable to correctly load column information for an XML index, which caused an "Assertion failed" exception during the comparison. Fixed.
  • NULL keys in DataDiff: when DataDiff generates a synchronization script for table data, it addresses specific records by using "WHERE key1=value1 [AND key2=value2...]" syntax. The key columns in a data comparison are usually not-nullable, but occasionally they can contain a NULL value. Previous versions of Diff didn't recognize such situations and scripted the condition as "key=NULL", although the correct syntax is "key IS NULL". This version scripts the NULL comparison correctly.
  • User-type dependencies: Previous versions of Diff didn't track usage of user-defined types (either scalar or table types) in (a) procedure parameters, (b) function parameters, (c) function return types, (d) inside table definition of a multi-statement table function. In all these situations the referring procs and functions must be dropped before re-creating the types (and afterwards re-created, if necessary). Fixed.
  • Missing schema name in IF NOT EXISTS: with "conditional" scripting enabled, previous versions of Diff used the "IF OBJECT_ID('object_name') IS NULL" condition for tables and other objects, but didn't include the schema name into the condition. This is fixed now.

New in AdeptSQL Diff 1.96 Build 97 (Jul 31, 2009)

  • Previous versions of Diff could crash with an AV trying to script USERs in a situation when a single database is loaded (as opposite to an actual comparison), the permission scripting is enabled and there is a certain combination of permissions granted from one user to another. Fixed.

New in AdeptSQL Diff 1.96 Build 92 (Oct 30, 2008)

  • Fixed the "List index out of bounds" error introduced in Build 90. Starting from that version, objects to be created and those to be dropped were accumulated in two different lists. Unfortunately, in some situations the wrong list was referred, resulting in either the "out of bounds" exception or incorrect scripting. The bug only affected the "Produce SQL/CREATE from..." and "Produce SQL/DROP from..." commands, not the regular synchro scripting.

New in AdeptSQL Diff 1.96 Build 90 (Oct 9, 2008)

  • New DATE, TIME, DATETIME2 and DATETIMEOFFSET types: these new internal types are now fully supported.
  • .NET-defined types: The Diff doesn't fully support .NET-defined types, including the built-in types HIERARCHYID, GEOGRAPHY and GEOMETRY, but it is now "aware" of them. Which means that if there is a table with some columns of these types, the columns are correctly compared and scripted. However, the types themselves are neither represented in the schema tree nor compared or scripted.
  • SPARSE columns are now fully supported. The SPARSE attribute is loaded from the database, compared and scripted (using "ALTER TABLE ... ALTER COLUMN ... {ADD|DROP} SPARSE" syntax).
  • COLUMN_SET XML columns are now fully supported.
  • DATA_COMPRESSION attribute for tables and indexes is fully supported.
  • Partial indexes: SQL Server 2008 allows to specify a WHERE condition when creating an index. These are fully supported in this update.
  • Unnamed constraints in SQL2008: The Diff recognizes automatic names that SQL Server gives to unnamed table constraints, marking such constraints as "unnamed" in the loaded schema and subsequently not including those name in the script. This feature didn't work in SQL2008, because the server now uses a slightly different format for the automatic names. Fixed.
  • ALTER PROCS in side-by-side view: The Diff has an option to re-script any views and procedures referring a column or table that has been changed. This feature was intended only for the actual synchro scripts, but (incorrectly) the dependencies were scripted in the side-by-side view as well. With a lot of procedures in the side-by-side panels, that could make the textual comparison very slow or even crash. This problem has been fixed by not allowing the dependencies to be scripted for the side-by-side view.
  • Adding NOT NULL columns: the server doesn't allow adding NOT NULL columns unless they have a DEFAULT. The Diff knows this and assigns a temporary DEFAULT that is dropped afterwards. However, SQL 2005 and 2008 only require this default if the table has data in it (whereas SQL2000 requires it for any table). The new version of Diff recognizes this nuance as well and doesn't generate temporary DEFAULTs when adding NOT NULL columns to empty tables in SQL 2005 or 2008.

New in AdeptSQL Diff 1.95 Build 89 (Sep 29, 2008)

  • Optional "USE target_db_name" statement at the beginning of any script: Although explicitly setting the current database is normally not required when you execute synchronization scripts in the Diff itself, it may be useful if you save the script and then run it from an external tool at a later time. This version of Diff includes an option to enable scripting of "USE target_db_name" at the beginning of any script the Diff generates. This options can be found on the "Scripting/Schema Level" page of the Options dialog. It is OFF by default, so the Diff won't generate the database selection command unless you ask it to.
  • PAD_INDEX made optional: an option was added to the "Ignored details" options page to ignore or compare the PAD_INDEX attribute.
  • Dependencies of principals: In SQL2005 and newer, a lot of various database objects (and not only the usual 'sysobjects' like tables or procs) can be made owned by a specific database principal (user, role, etc) using the ALTER AUTHORIZATION statement. If the principal is being removed, all these objects must be first "re-owned" to their schema or to DBO. Previous versions tracked those dependencies only for 'sysobjects' and named schemas, this update also tracks UDTs, XML schema collections and even object types that are not otherwise supported by Diff 1.xx (encryption, service broker, assemblies)
  • "exec sp_addtype" vs. "create type": although these two ways of defining a UDT are equivalent, there is a quirk in how SQL Server handles them: "sp_addtype" additionally grants REFERENCES access to PUBLIC, whereas "create type" doesn't. Previous versions of Diff counted that as a difference in permissions, which was technically correct, but confusing. In this version, this particular "permission" is used by set an "old style" flag in the UDT object, but is not added to the permission list. The "old style" flag determines how the UDT definition will be displayed in the side-by-side view, but otherwise is ignored. It is not considered as a difference when comparing the types.
  • Look-ahead for objects to be scripted: when an object is going to be dropped and the Diff is removing its dependencies, it is often possible to "move away" the dependent objects non-destructively as opposite to dropping them. For example, when dropping a UDT, we can either drop columns of that type or revert them to the underlying physical type. Obviously, if a column or table is going to be dropped anyway within the same scripting operation (e.g. the entire database was selected for scripting), it's easier to drop them at this point. Otherwise, the Diff must not destroy columns it was not asked to sync them. In previous versions, the Diff lacked the mechanism to look ahead and check if a particular object was selected for scripting. Now this has been added.
  • Restore permissions after ALTER AUTHORIZATION: re-owning an object resets all permissions granted to it. When re-owning in the process of dropping a principal, permissions must be restored afterwards. Previous versions of Diff were aware of that, but didn't always restore the permissions correctly. Fixed.
  • Can't script permissions to owner: it is possible to construct a situation where in the "source" database a securable object such as a table has permissions granted to a pricipal, whereas in the target DB the same object is owned by that same principal. The Diff would then attempt to sync those permissions, however the SQL Server does not allow GRANTing permissions to the principal who owns the object. In this version, the Diff would recognize this situation, issue a warning and skip those permissions.
  • Permissions for XML Collections: Although XML Schema Collections were added to recent versions of Diff, permissions for them were not loaded from the database. Fixed.
  • Ownership for types and XML collections: these objects were supposed to support explicit ownership (scripted by ALTER AUTHORIZATION), however previous versions didn't correctly load the ownership information from the database. This is fixed now.
  • Index options in brackets: before this update, the Diff used obsolete syntax for index options such as FILLFACTOR ("CREATE INDEX ... WITH FILLFACTOR=..."). This worked with the particular FILLFACTOR option, but other options such as PAD_INDEX require the new "WITH (option_name=ON|OFF,...)" syntax. This is implemented now: depending on the target server version, CREATE INDEX is scripted with either old or new syntax. See also (above) a new setting to ignore changes in the PAD_INDEX.
  • Owner created with "Ignore owners": in a rare situation when the databases are compared with "Ignore owners", objects may be associated with each other even when they belong to different schemas, e.g. [User1].[Table1] in the source db will be matched to [dbo].[Table1] in the target. This is by design. When such a table is synchronized, all changes will apply to [dbo].[Table1]. This includes the situation when the target table must be dropped and re-created: the new table is created as [dbo].[Table1], not [User1].[Table1]. So far so good, except that in previous version the Diff would first create [User1] (incorrectly) before re-creating the table (correctly, as [dbo].[Table1]). The problem has been fixed in this update: the Diff now doesn't create the user that shouldn't be in the target DB.
  • Must not drop DBO: In some rare situations when comparing with "owner mapping", the Diff would try to remove the built-in user/schema [DBO] from the target database. In this version, none of the built-in schemas/users can be dropped.
  • Missing "USE" for a new database: when the Diff was configured to include the CREATE DATABASE scripting, it didn't switch to the created database before scripting any other objects. Fixed. Note that in this situation the "USE" statement is generated unconditionally, regardless of the new "Script USE" option described above.
  • The recently added check for the minimum required permissions does not work in some [rare] situations, breaking the scan with "Not enough permissions to even attempt reading schema" error, although in fact the schema could be read. In this version, the permission check still exists, however it only displays a warning without breaking the scan.
  • Spaces in DataDiff row filter: if you put some spaces in the row filter expression field of DataDiff (as opposite to just leaving it empty), a "WHERE " clause with this empty expression would be included in the resulting query, which subsequently fails. Generally, DataDiff doesn't validate the filter expression so you are responsible for any incorrect syntax, but in this particular situation the spaces are not easily visible and may be very confusing. This version of Diff trims any leading/trailing spaces in the filter expression, so an "expression" containing only spaces will be ignored.
  • [N]TEXT and IMAGE data conversion path: when column type changes from TEXT or NTEXT to IMAGE or back and the Diff must preserve the data, simple data conversion to the destination type won't work. In these situations, the Diff now uses two-step conversion through VARBINARY(MAX) and [N]VARCHAR(MAX) types. The correction applies both in schema sync (when the Diff tries to preserve data while changing a column type) and in DataDiff, when synchronizing data in sever-to-server mode.

New in AdeptSQL Diff 1.95 Build 83 (Mar 20, 2007)

  • Exception after dropping objects and automatic one-side rescan. When you script DROPs for all objects in a category (e.g. all procedures) then apply the script to the right-hand database, and the Diff is configured to auto-refresh schema after running a script, the Diff would crash. This happened because the Diff only reloaded the right-side (changed) schema and some references to the deleted objects remained in the other one. Fixed: now the Diff properly cleans up its internal links before each comparison.
  • Exception reading FOREIGN KEYs (rare). There appeared to exist a rare situation when the Diff would not correctly read from the database the referenced (target) table for a FOREIGN KEY. That would result in an "Access violation" exception while comparing or later displaying the schema. Although we were unable to reproduce the situation or fully understand how it could happen, additional checks have been added to the schema reader to report and discard any "incomplete" FKs before they are added to the in-memory schema model. We'd be very much interested to see examples of such incomplete FKs and the relevant SQL scripts.
  • Exception reading indexes on SQL 2005 (rare). Each UNIQUE and PRIMARY KEY constraint in MS SQL has its underlying index. The server keeps records for the constraint itself and for its index in separate system tables. The two are associated by name and the server normally keeps both names in sync even when either constraint or index is being renamed. However, it seems to happen sometimes that a PK name gets upper-cased on the server whereas its corresponding index name remains as originally entered. When that happened, the Diff would fail to associate an index with the PK, causing an "Access violation" error. This only affected reading indexes from MSSQL2005 servers. The only way we were able to reproduce the problem was by manually changing one of the names in [sysobjects] (under SQL 2000), then re-attaching the database to a SQL 2005 server. Since several users have reported the problem, it is likely that such inconsistency in the system tables was caused by some SQL 2000 database management tool which directly modified the system tables before the database was transferred to SQL2005. In this version, the schema reader has been modified to properly handle such situations.

New in AdeptSQL Diff 1.95 Build 81 (Dec 5, 2006)

  • DataDiff scripting generated all string literals as N'...' even if the column was not a Unicode one. Fixed: now the Diff generates N'...' literals only for Unicode columns (NCHAR()/NVARCHAR()/NTEXT()).
  • DataDiff scripting didn't use the Uppercase/Lowercase formatting options. Fixed.
  • In SQL 2005, when scripting REVOKE permissions from users, schemas and other entities, the last version of Diff would not include the type qualifier (as in 'revoke control on USER::user1 from user2') required by the MS SQL syntax. Fixed.
  • When changing from a column-level permission to a table level one, the last version of Diff would script them in a wrong order, e.g. 'GRANT UPDATE on T to user2; REVOKE UPDATE on T(col1) to user1;'. In this situation revoking the old column-level permission is redundant, but even if it is done, it must be done first. Fixed.
  • Some static elements on the connection panel were not reliably repainted when the Diff window was coming to the foreground. Fixed.

New in AdeptSQL Diff 1.9.0.61 (Aug 1, 2006)

  • This version is an interim update with just a couple of minor corrections concerning comparison of comments and scripting of indexes. More changes are on the way.
  • Bug fixes
  • Fixed the problem with ignoring comments in procedures, etc. Even with "[x] Ignore comments" option set, the Diff sometimes marked a difference if a comment was present in one procedure, but not in another.
  • Syncronizing an index, the Diff scripted DROP INDEX/CREATE INDEX twice. Fixed.
  • The DataDiff now ignores TIMESTAMP columns by default, it also won't script them in INSERTs even if they are compared.
  • Pending changes (for the next update)
  • Full support for XML indexes and non-key index columns (the INCLUDE() clause) in SQL2005.
  • Support of partitioning syntax ("ON partition_schema(column)") for tables and indexes in SQL2005.
  • More precise control over permission scripting should be added, including the options to either synchronize permissions or to re-create the original permissions when an object is re-created. The same should be done for extended properties.

New in AdeptSQL Diff 1.9.0.61 (May 10, 2006)

  • This update includes several important corrections in script generation and reporting.