Changes and improvements

• The pulldown list of recently used filter expressions in DataDiff row configuration dialog has been changed from table-specific to global, so that previously entered filter expressions can be reused with different comparisons. The selected/entered filter expression, of course, remains table-specific.

Bug fixes

• Unlike SQL2000, SQL2005 allowes (under certain conditions) to define constraints PK or UNIQUE on computed columns. The Diff wasn't aware of that and so didn't script column-level constraints with computed columns. Fixed.
• In the "ALTER TABLE ... ENABLE/DISABLE TRIGGER..." statement the Diff scripted the trigger name in [owner].[name] format, which was incorrect: in this context both SQL2000 and SQL2005 require the trigger name only. Fixed.
• The Diff did not handle correctly duplicate foreign keys. For example, if you happen to have in one table foreign keys [fk1] and [fk2] with identical definitions, and the same 2 FKs in the opposite table, both [FK1] and [FK2] in the left-hand table would be be incorrectly associated with the same [FK1] on the right-hand side. Fixed.
• The Diff was ignoring the 'snap-to-default-button' Windows interface option (the one which causes the cursor to be automatically positioned over the [OK] or other default button on a modal dialog). This has been fixed, except for single-button [OK] message boxes.
• Issues running/stopping SQL: The button in the SQL editor that was supposed to stop running script didn't work. If you tried to close the window while the script was running, this caused an AV exception. Fixed.
• After breaking script execution, the Diff didn't show the 'execution pointer' at the next SQL statement to be executed. Fixed.
• Refreshing after running a script: When the Diff executes any SQL in the editor window, it asks if the [possibly] affected schema should be refreshed. If you say No, it should reset the change flag and not bother you again. Instead, the Diff then keeps asking about refreshing the schema every time you try to close an SQL view. Fixed.
• After executing a script generated by DataDiff, the program asked to refresh the schema (and did so), whereas in fact it should have asked to refresh the data comparison. Fixed.
• Persistency in DataDiff parameters: In data compare, the "Don't keep identical records in memory" and the "Order data on the server..." were persisted differently, although logically they both are table-specific settings. Fixed: now they both are saved with table-specific DataDiff configuration. Besides, when you compare several tables one after another (and it is the first data comparison for this pair of tables), the DataDiff initially uses these settings from the previous comparison.
• The MRU logic in the server selection combobox didn't work correctly: you would end up with a lot of identical items in the list. Fixed: Beta 58 removes duplicate server names saved by the previous version and tries not to make duplicates again.
• The auto-completion feature in the server selection combobox sometimes worked incorrectly, replacing the entire server name you've typed in. This had to be fixed by disabling the auto-completion.

Changes and improvements

• Until now, the Diff would not allow to run multiple instances of itself. This was done so for historical reasons, but the limitation doesn't make much sense any more. So now you can run several instances of Diff if you like, with a separate comparison in each of them. Just note that any changes in the program configuration (Schem Options, window layout, etc) are saved when the Diff is closing, so the last instance you close overwrites any previous settings.
• There is also a new command line parameter '/last' or '/l', which emulates (and improves) the original single-instance behaviour: if another instance of Diff is already running, the new instance passes to it its command-line parameters, activates the original instance and exits. If the original instance sees a comparison file (.ASQ) passed to it, it opens the comparison.
• Keeping the tree position: Suppose you select an object (e.g. a table) in the schema tree, then run some SQL that removes or renames that object, then refresh the tree. The Diff can no longer find the previously selected object in the tree, so it collapses its parent node. Although not exactly an error, this is inconvenient. The correct response would be to keep the list open and select an object at approximately the same position within the list. Done.

Bug fixes

• Reported 'Invalid typecast' exception in some situations while loading table constraints. Fixed.
• With "[x] Always script owner names" option, Diff 1.90 scripts owner names for column types as well. This is valid in SQL2005, but not in SQL2000. Fixed: column types are now only scripted with schema names for SQL2005.
• Comparison of column names in indexes was always case-sensitive, regardless of the actual case-sensitivity settings. Fixed.
• When you specified an "owner mapping" on the Comparison/Names option page, the Diff uppercased the owner names. However, in combination with case-sensitive name comparison it means that the mapping wouldn't work properly. Fixed: the names specified for the owner mapping are now kept exactly as entered.
• When you configure to script constraints with columns (in Scripting/Tables), and a column name is bracketed (e.g. [Last name]), the Diff fails to associate any constraints with such column and so doesn't script the constraints at all. Fixed.
• Uppercasing in the side-by-side view: With comparison case-sensitivity turned ON (in Comparison/Code) and keyword formatting set to lowercase/uppercase (in Scripting/Formatting), we have a confusing situation: differences like 'PROCEDURE' vs. 'procedure' show up in the schema tree, but aren't visible in the side-by-side view, since it shows both sides already upper-/lower- cased. Although not exactly a bug, this is inconsistent and confusing, so a new option has been added in this version: "[x] Don't change case in side-by-side view", just below the case-controls in Scripting/Formatting. You can still have nice formatting for the output script, but will see things as they are in the side-by-side view.
• The side-by-side view didn't correctly display a national character with code 255. This was reported by a Russian customer for whom the Diff couldn't show the small Cyrillic letter 'ya'. Fixed.
• Comparison not saved: When you start the Diff and there are connection parameters remaining from the previous session and then you try to save the comparison into a file, it will not be saved correctly unless you touch some of the connection parameters. The same error leads to the SaveAs command being disabled after saving a comparison: the Diff should disable SaveAs only when there is no connection parameters to be saved, but it confused 'no parameters' with 'parameters not changed'. Fixed.
• Incorrect resizing: When Diff's main window is just small enough to fit the connection panel and you start a comparison, the main window automatically resizes itself to fit the schema tree, however the sizes of the status bar and the toolbar are not adjusted accordingly unless you manually resize a bit more. Fixed.
• The "File/Close all scripts" menu command, once clicked, not only closes all open SQL windows, but also stop the SQL viewer from appearing until you refresh the comparison. The same command remains enabled even when there are no editor windows to close. Fixed.
• Message panel at startup: When the Diff starts, the message panel at the bottom is visible, although it should not be shown until a comparison is open and there are some messages to display. Fixed.
• Missing PKs: When you making an update script for several selected table and there are PRIMARY KEY constraints in several of the tables, the Diff only script those constraints from the first of the selected tables, but fails to do so for the rest of them. Fixed.

Changes and improvements

• The pull-down server list in the connection panel has been redesigned: it now visually separated in two parts: the upper one works as a MRU list, remembering servers recently selected or typed in manually, the lower one lists servers known by their aliases, etc.
• Help file finalized: The help file has been context-linked to the relevant windows and dialogs of the program, especially to individual pages of the schema options dialog.

Bug fixes

• There was a "Division by zero" when you tried to compare side-by-side a long (~200 lines or more) procedure on one side against no code on the other. Fixed.

Bug fixes

• A DataDiff error: A logical error in the data comparison algorithm caused AV exceptions when comparing tables with "don't keep identical rows" option and some columns excluded from comparison. Fixed.
• Scripting direction reversed: In one of the recent updates, difference scripting for the entire schema got reversed: the Diff would generate DROPs instead of CREATEs and vice versa. That only happened when you selected the root schema node, any sub-nodes like tables or procedures were scripted correctly. Fixed: the scripting direction is now restored back to normal.
• Incorrect script for synchronizing enabled/disabled triggers: the table name was missing in "ALTER TABLE ??? ENABLE TRIGGER ...". Fixed.
• The column configuration dialog in DataDiff did not recognize columns as belonging to the PK (and therefore doesn't automatically set them as key columns) for column names enclosed into the [...] or "...". Fixed.
• The new exception handler has been finally configured to show up only for critical exceptions such as access violations. Exceptions that can happen as a part of normal work (e.g. pulling down the database list with bad login parameters) the application handles as usually by showing an error message box.

Changes and improvements

• Help file finalized: The help file has been context-linked to the relevant windows and dialogs of the program, especially to individual pages of the schema options dialog.

Changes and improvements

• The updated help file: The help file is now fully in synch with the program.

Bug fixes

• Fixed an access violation comparing renamed columns.
• There was an access violation in DataDiff, when it is run on a pair of tables that contain no differences, with the options to not retain identical records. The DataDiff shows a blank data diff screen, followed by the exception. Fixed.
• There was Access violation scripting a column with user-defined type and a global default or a rule bound to this UDT. Fixed.
• Option "Ignore changes in expressions / [x] in CHECK constraints" only works for named CHECK constraints. This is not actually a bug, since unnamed CHECK constraints can only be associated with each other if they have identical expressions. This issue has been addressed by documenting it in the options dialog: "[x] In CHECK constraints (only for named ones)". Nothing else could or should be done about it.
• Selective loading for a specific owner was broken in the last Beta: all objects were loaded regardless of what owner filter was set. Fixed.
• When the owner filter is set so that no objects are loaded, there is an access violation while loading the schema. Fixed.
• DataDiff running in the "discard identical records" mode did not show in the status bar the number of identical (discarded) rows. Instead, it incorrectly displayed 0 identical rows (as none are kept in memory). Fixed.

New and changed features

• Option pages restructured: "Ignored schema details" has been split into "Schema Scan/Selective loading" (options which affect loading) and "Comparison/Other ignored details" (things which get loaded but aren't compared)
• A new block of options for ignoring changes in expressions (in computed columns / DEFAULTs / CHECKs) has been added to the "Other ignored details" page, to work around the problem with SQL2005 re-formatting these expressions (the problem being that when you compare equivalent databases between SQL2000 and SQL2005, you can get a lot of "false positives" from the re-formatted expressions).
• A new option added into the "Schema Scan/Selective loading" page: a checkbox indicating whether or not the Diff should obtain counters for various kinds of schema objects it is going to read. Pre-loading the counters allows to display realistic progress indicators, but obviously the query itself adds some time to the schema scan. This option does not affect the resulting schema.
• The "Schema Scan/Ownership" page has been removed, options from it moved to several other pages: (a) the filter-by-owner settings has been moved into the new "Selective loading" page; (b) The scripting-related settings page moved to "Scripting/Identifiers"; (c) the owner mapping has been moved into the new "Compare/Name comparisons" page.
• The "Comparison/Renaming support" page has been removed, as the options it contained are not supported in this version.
• Automatic case-sensitivity option: The case-sensitivity option for object names has been moved into the new "Compare/Name comparisons" page and extended to include 3 values: case sensitive, insensitive and "Automatic", where the case sensitivity is determined according to the collation order of the comparison databases (name comparison is only case sensitive when both databases use case-sensitive collation order). This new setting is now the default.
• A new menu item 'Hide changed objects' has been added to the schema view filters. Now if you need to view left-only and right-only schema items, but not the changed one, you can do so.
• The Beta now uses an extended exception reporting component (from www.madshi.net), so if any AV happens, you will be able to send me a detailed bug report by just pressing a button.
• The provider name in the connection strings has changed from "SQLOLEDB.1" to "SQLOLEDB". Normally both work just the same, but presumably in some rare situations using the version specific provider name can cause the "Provider cannot be found" error message.

Bug fixes

• Incorrectly implemented monitoring of the multi-threaded schema loading sometimes caused the Diff to occupy 100% of CPU time, slowing down all other applications (as well as the schema scan itself). This only happened when one of the loader threads completed ahead of the other one, e.g. when comparing a local database with a database on a remote server. Fixed.
• The same problem as described above occured in the DataDiff data scan. It is also fixed.
• MS SQL 7.0 compatibility restored: Some of the recent dependency-scan additions were not compatible with MS SQL 7.0. This version fixes the problem with an axe: it just doesn't read dependencies for SQL 7.0. A better solution may be possible in one of the next updates.
• A bug in the server scan dialog prevented some nodes of the server tree from being added to the list of recent servers. Fixed.
• The Diff still associated procedures and other code objects in SQL 2005 with their owner (user), rather than their named schema. The same problem existed for the user-defined types. Fixed.
• The user-defined types were always scripted without their schema name, which is an error if the type was defined outside the current schema. Fixed: the format of UDT names is not controlled by the same options as other objects (see "Scripting/Identifiers" option page)
• Non-latin object names caused errors during the "checking for data" phase of the schema loading. With the data check disabled, such objects were loaded, but their names weren't correctly displayed in the schema tree. Both problems have been fixed in this version, although you must make sure to set the correct encoding (Tools/Encoding...) before running the comparison. Note that the Diff still doesn't support several different character sets in the same comparison.
• When reading database schema with filtering by owner (or rather, schema name), the Diff generated some queries that work for SQL 2000, but return an error in SQL 2005. Fixed.

New and changed features

• In SQL2005, the Server Management Studio stores database diagrams in the [dbo].[sysdiagrams] table and adds a number of stored procedures to manipulate the diagrams, also as a part of the [dbo] schema. They are essentially system objects, but they are not marked as such so the Diff would loade them as part of the schema. This version uses the 'microsoft_database_tools_support' extended property which Microsoft tools kindly attach to such objects in order to filter them out.
• Added an option to ignore any differences in constraint names, as long as the constraints are equivalent. See the "[ ] Ignore name changes..." checkbox on the Options/Scripting/Tables page of the options dialog.

Bug fixes

• When system tables are included in the scan, trying to detect data in some of them (e.g. [sys].[sysrowsetcolumns]) results in the server reporting an error. Fixed: now system tables aren't scanned for data, even if the others are. The Diff assumes that system tables always contain some data.
• SQL Editor options (such as colors of syntax highlighting) were not loaded correctly when the Diff starts, which means it was impossible to make persistent changes to the syntax highlighting. Fixed.

Bug fixes

• When a table uses a function in a computed column and this function refers back to the table, the new dependency-checking algorithm (used since Beta48) goes recursive and crashes the Diff with a "stack overflow" message. Fixed.
• After a table is re-created to synchronize column order, the Diff reloads just the target schema and somehow the difference flag in the columns collection node remains set. So although all differences are gone, the Diff still displays the collection as changed (another refresh does clear it). Fixed.

Changes and improvements

• One of the [optional] steps in loading database schema is checking if each of the tables has data in it. For schema scripting it is only necessary to know whether or not there are data rows, not the exact number of them. However, before this version, the Diff did use COUNT(*) to obtain the row counts, which would considerably slow it down when the tables are large. Now the option (in "Ignored details" option page) has been extended to include 3 positions: no row check at all (assume all tables have data), actually count the rows (using SELECT COUNT(*)...) and the new option to quickly check for presence of data (using SELECT TOP 1...). This new option is now the default one, it provides all necessary information and it is fast regardless of the table sizes.
• There are two options in the Diff controlling the "SET QUOTED_IDENTIFIER" settings. One is "[x] Generate QI" checkbox which affects BOTH comparison and scripting, so if you un-check it, the changes in QI will not be hightlighted in the schema tree. The other is "(o)Always/( )When needed" switch which determines whether the SET QI should be scripted always or only when the object actually contains any quote character. This one, however, only affected scripting, but not comparison, which was always done on the "when needed" basis. This was rather inconsistent, so in this version the "Always/When needed" switch has been extended to control comparison as well. It means that if you set it to "Always", ALL procedures (triggers, views, functions) with different QI settings will be marked as changed, even if they don't have any quotes inside.
• The code which checks whether the "SET QUOTED_IDENTIFIER" setting is relevant for the procedure (view, etc) simply looked for any quote character anywhere in the procedure body. It could return false positives if the quote character was in a comment. Although it didn't actually cause any trouble (except for maybe an extra "SET QI" scripted sometimes), it was was replaced by a smarter code, which properly ignores quotes in comments or single-quote string literals.
• This one is not really a bug, but a situation in DataDiff which can be confusing: when you compare very large tables, and specify "[x] Order data on server by key columns" and the key column is not indexed, it is likely to cause a timeout while waiting for SQL Server to sort the data. A note informing about such situation has been added below the "[x] Order data" checkbox.
• The DataDiff progress dialog has been extended to include important statistics such as the number of processed rows, the number of buffered rows and the total amount of memory allocated to keep them.

Bug fixes

• When DataDiff started scanning the data, the queries were issued in such a way that there was a delay, proportional to the table size, before the first record could be fetched. For extra-large tables (> 1M records) that would result in a timeout and a blank DataDiff window. Fixed: now the retrieval of data starts immediately ('SET NOCOUNT ON', forward-only server-side cursor).
• The "Checking for data" phase of the schema scan did not display itself in the progress dialog. Fixed.
• When you try to close the Diff, then answer [Yes] when it offers to save the comparison, the press [Cancel] in the "Save As..." dialog, the application closes instead of returning to where you were. Fixed: now cancelling the "Save As..." dialog as well as any errors while saving the comparison return you to the main Diff window.
• A tooltip in the Schema Options dialog under Scripting/Formatting/Keywords says "not implemented". In fact the option does work. Fixed (the tooltip removed).
• Beta48 has somehow revealed an old bug comparing indexes, which is that an index existing in the right-hand DB but not in the left-hand one would be marked as changed, but the change was not propagated to the table level. Fixed.
• While DataDiff was loading data from tables, it did not immediately react to the user clicking the [Cancel] button. Fixed.

Changes

• The SQL2005 modifies CHECK and DEFAULT expressions, re-formatting each numeric literal and adding parenthesis around them. As the result, comparing equivalent expressions between SQL2000 and SQL2005 returns a mismatch which can never be synchronized. This version adds a "[x] Smart comparison for expressions" option on the "Comparison/Text and name comparison" option page. When the smart comparison is enabled, the Diff ignores (a) parenthesis around the numbers (unless it's a function call), (b) optional prefix '$' for money columns and (c) compares the numeric literals as numbers (so e.g. '0.01' and '1.0E-2' would be considered equal).
• The Diff now correctly handles multi-level dependencies between schema-bound views and functions, dropping and re-creating all such objects throughout the whole dependency chain.
• The table re-creation sequence has been changed so that triggers and constraints (or at least triggers) be scripted after inserting the data, so the triggers won't fire on that INSERT.
• When the column order was different and the Diff configured to detect changes in the column order, it marked the the entire table as changed, but not the 'Columns' node. So it was not obvious what the change actually was. This version does mark the 'Columns' collection node as changed and reports the change of column order in the hint popup.
• The 'SET QUOTED_IDENTIFIER' and 'SET ANSI_NULLS' settings (before procedures, views, etc.) are now scripted in the same batch, just to make the script a bit more compact.

Bug fixes

• The system stored procedures which manipulate users, logins and groups (sp_dropuser, sp_grantdbaccess, etc) can't be executed in a transaction. The Diff now understands this and scripts such statements outside the transaction.
• Finished checking for memory leaks, some more of those have been found and fixed.
• When re-creating a table, the Diff would drop constraints to avoid name conflicts. However, the previous version would drop all constraints but not FKs. Fixed.
• When a table was being recreated, the Diff scripted permissions and xprops twice. Fixed.

Bug fixes

• The Diff didn't check if there are FKs dependent on an index being dropped. Such dependencies must be checked and the FKs dropped, if necessary. Done.
• If the left-hand table has its clustered index on some column A and the right-hand table has its clustered index on column B, the Diff must drop the existing clustered index before it can create a different one. Fixed.
• Bug in B46: when dependent constraints are being dropped, the Diff scripts each of them twice. Fixed.
• Bug in B46: When synchronizing a named constraint with unnamed one, the Diff would first script renaming (which is all that was needed), then try to add the constraint as a new one. Fixed.
• Cleaned up some non-critical memory leaks (in configuration parameters).

Changes

• Optimized "SET QUOTED_IDENTS" and "ANSI_NULLS": if you enable generation of these settings before stored procedures, functions, views and triggers, the Diff used to put them before each of the generated objects. This version scripts them once at the beginning and then only when a setting changes.
• DataDiff: It might be a good idea to retrieve data sorted by the key column. This is not strictly required for the comparison algorithm to work, but this way (a) you immediately get the data correctly sorted in the grid and (b) with the "Discard identical rows" option enabled, you are making sure that identical rows will be loaded more or less simultaneously from both tables and therefore the program will be able to discard them as excepted. So this version include a new option in the DataDiff row configuration page to sort the incoming data.
• The logic of The "Recreate tables" option has changed. When enabled, the option used to mean that the Diff must re-create table if there are any changes to any columns and as a side effect it enabled order-sensitive column comparison. Now the option has been re-phrased as "Keep column order, re-create table is necessary". It no longer forces the Diff to re-create a table if the synchronization can be done without that, but the Diff will do whatever necessary to synchronize the column order. The option is now enabled by default.

Bug fixes

• When a column with some constrains is recreated, and constraints of this type are configured to be scripted with column (e.g. 'x int unique'), the Diff would drop the PK, drop the column, recreate the column with constraint, then tried to add the same constraint again, which was an error. This is fixed now.
• When the Diff has to drop a PK or UNIQUE constraint in order to change it or its column(s), it would also drop any FKs depending on this constraint's index. When restoring the PK/UNIQUE constraint, it would forget to restore the FK as well. Fixed.
• To preserve data, the Diff scripts "change via copy" SQL sequence when a column has to be re-created and there is data in the table. As it turned out, it does so also for computed columns, which is an error, because data can't be copied into a computed column and such columns should have been simply added/dropped. This version adds the logic to recognize when the data should or should not be preserved when re-creating columns.
• The Diff didn't detect changes in constraint name as a difference to be synchronized, unless both constrains were named. Ignoring names is OK for 2 "unnamed" (automatically named) constraints, but not correct if at least one of the names is explicitly specified. This version correctly synchronizes constraint names and, if it's the only change, does so using 'sp_rename' without actually re-creating the constraint.
• When the scema scan starts, the first status text in the progress dialog should be "Connecting to...", for each of the two databases. However, the Diff displayed it for the left-hand DB, but not for the right-hand one. Fixed.
• DataDiff: there was a problem loading data from NTEXT/NCHAR columns. Fixed.
• With the "Lexical comparison" (for SP, etc) option turned on, the Diff would incorrectly parse and compare string literals. E.g. two strings '; hello' and ' hello'" would be seen as identical. Fixed.
• The 'Discard unchanged rows' option is DataDiff doesn't work if the rows contain any column(s) with data conversion (e.g. 'int' vs. 'float'). Fixed.