AdeptSQL Diff older versions

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.

Known problems

• Changing schema while loading: A user has reported that certan changes in the database schema, made through a separate connection while it is being loaded into the Diff, would crash the program. Future versions of Diff might implement some (possibly optional) mechanism for locking the database schema during the schema loading operations. Meanwhile, it is recommended to avoid doing any changes to the database while the Diff is loading it.
• Crashes in single-DB mode: despite the corrections made in in last release, some users still report crashes when trying to create scripts in single-database mode. We haven't been able to reproduce the problem so far, so please do send us a bug report (with whatever details you think might be relevant) if you encounter any such problem.

Bug fixes

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

• Synonyms in SQL 2005/2008 are now fully supported (loaded, compared, displayed and scripted).

Bug fixes

• "Ignore expression" options didn't work. There are 3 checkboxes on the "Comparison/Details to ignore" options page which allow to completely ignore changes in expressions in the following situations: (a) in computed columns, (b) in DEFAULTs and (c) in CHECK constraints. These options are normally not used, but might be needed when you compare a SQL 2008 database against a similar DB on an older server version. SQL 2008 server internally reformats expressions in a different way than all previous versions, so you might end up with a lot of "false" differences in DEFAULTs/CHECKs/computed columns. These "Ignore" options were supposed to provide a "last resort" solution to this situation, but unfortunately, previous versions of Diff just ignored these settings. This is fixed in Build 96: with the "Ignore expressions" options enabled, any change in expressions is treated as a "minor" difference which is not displayed in the schema tree nor synchronized. The settings for DEFAULTs and CHECKs also affect global DEFAULTs and RULEs, respectively. Swiching the "Ignore" settings displays or hides changes in expressions without the need to actually re-load and re-compare the entire schemas.
• Upper-casing long scripts: upper/lower-case formatting only worked within the first 64K of a very long selection. Fixed.
• Changing between scalar and table functions: if one database has a scalar function and in the other DB the function with the same name is defined as a table function, previous versions of Diff would try to synchronize them using ALTER FUNCTION. However, ALTER can't change the type of the function, so in this case the function must be dropped and re-created. The Diff now recognizes the change in the function type (scalar vs. inline table-valued vs. multistatement table-valued function), displays the change in the schema tree difference hint and scripts it correctly.

Known problems

• An AV of unknown origin was occasionally encountered in Build 95. This problem has NOT been fixed in Build 96, although other corrections in the code might (or might not) have affected the bug.

Changes and improvements

• Support for partitioned tables and indexes: Previous Diff versions didn't recognize or script the partition clause in tables and indexes ('ON partition_name(part_column)'). This version does script this clause correctly, although it still does NOT display, compare or script definitions of partition schemes (PS) and functions. When re-creating partitioned tables or indexes, the Diff attempts to preserve the original partitioning in the target (rather than the source) database. More specifically:

  • Partition changes are supposed to be ignored. Make sure the "Ignore filegroups" option in "Options/Compare/Details to ignore" is checked! If it isn't, the difference in partition schemes will appear as filegroup difference, but the Diff won't synchronize it.
  • If a new partitioned table (or index) is scripted, and the same PS exists in the target database, it will be scripted as partitioned. If there is no corresponding PS on the target, the Diff won't try to create the PS, will report a warning and script the object without the "ON part_sch(col)" clause
  • When re-creating a table or index that was partitioned in the target database, the Diff will try to script it with the same partitioning as before. If the partitioning column is missing on the source side, the Diff will issue a warning and either substitute it with the partitioning column from the source side (if such exists), or will remove the partitioning.
• Ignoring index names: An option has been added to ignore name changes for otherwise identical indexes. The "Options/Comparison/Statistics" page is now titled "Statistics and Indexes" and the new option is added there. The reason is that Diff has always had a similar option for named constraints, and index names as usually no more important than constraint names: as long as a table is correctly indexed, nobody would care about the index names.

• Replacing unsupported data types: When synchronizing from a newer version of MS SQL Server to an older one (e.g. from SQL2008 to SQL2005), the Diff now recognizes column data types that are not supported on the target, issues a warning and scripts the affected column(s) with the closest alternative type. This applies to the following types:

  • TIME, DATE, DATETIME2 and DATETIMEOFFSET are all scripted as DATETIME when scripting from SQL2008 to any earlier version;
  • Any CLR type is scripted as VARBINARY(n) or IMAGE when scripting from SQL2008/2005 to SQL2000 or earlier;
  • XML columns are scripted as NTEXT when scripting from SQL2008/2008 to SQL2000 or earlier.

  Please note the following limitations in Diff's handling of such situations:

  • CLR types GEOMETRY, GEOGRAPHY and HIERARCHYID are predefined in SQL Server 2008, however the Diff treats them as just regular CLR types and will script them as is into SQL2005 target server, even though the target won't recognize them. More generally, since the current version of Diff does NOT track loaded assemblies, it will script any columns of CLR types regardless of whether or not assemblies defining these types are loaded on the target server.
  • The Diff does not specially handle a situation when a column already exists on the target with the correct 'replacement' type. For example, if the left-hand database on SQL2005 has an XML column T.A and in the right-hand SQL2000 database the same column T.A is NTEXT, the Diff will still see the columns as different and try to sync them. Since XML columns aren't possible on the SQL2000 target, the Diff will script it as NTEXT, ignoring the fact that it is already NTEXT.
• SQL2008 introduces a new type of permission, GRANT VIEW CHANGE TRACKING, which was not recognized by previous Diff versions. This permission is supported now.
• GRANT formats unsupported on SQL2000: Starting from SQL2005, permissions can be applied to various types of schema objects using "object_class::name" qualifiers (as in "GRANT ... TO TYPE::MyInt..."). Previous versions of Diff supported this syntax, but didn't check if the target SQL Server version does. Now the Diff issues a warning and ignores those permissions if the target server is SQL2000 or older.
• Permission types unsupported on SQL2000: There are several permission types that first appeared in SQL2005, such as CONTROL, TAKE OWNERSHIP and VIEW DEFINITION. Previous versions of Diff would script these permissions regardless of the target server. Now the Diff issues a warning and ignores those permission if the target server is SQL2000 or older.
• There are changes in the "Schema/Permissions" schema view node, which displays database-level permissions grouped by principal. In previous versions, just principal name (e.g. "User1") was displayed in each sub-node, which made it easy to confuse with Principals subnodes. Now the display is changed to "To [principal]" for the subnodes and to "Database-level permissions" for their parent node, which makes the display visually distinctive. NOTE: if you access these nodes programmaticaly via Diff Automation objects, you might need to make changes in your scripts! The other change is that principals with only the standard GRANT CONNECT permission are not displayed there (but principals with revoked or denied CONNECT are displayed).

Fixed bugs

• The command to link renamed objects (in the context menu of the schema tree) finally works. In previous versions it worked for table columns, but not always for top-level objects such as tables or procedures.
• Extra ALTER AUTH for named schemas: When creating a named schema, previous Diff versions scripted CREATE SCHEMA with AUTHORIZATION clause, then scripted a separate ALTER AUTHORIZATION statement for the same schema. Although not exactly an error, the ALTER was redundant and is now removed.
• Synch owners for named schemas: when a named schema exists on both sides but is owned by different users, previous Diff versions didn't detect this as a difference. This version does show this as a difference and synchronizes the schema owner using "ALTER AUTHORIZATION..." statement. The feature can be disabled by checking the "Options/Scripting/Schema level/[x] Don't script users" option.
• Indexes partitioned on non-key columns: A non-clustered index (in SQL2005+) can be partitioned either on one of its key columns, or on a different (non-key) column from the same table. Since previous versions of Diff did not recognize partitioning at all, they would incorrectly report such an index with the partitioning column in the key column list. Fixed: this version correctly scripts partitioned indexes with the partitioning column in the key column list, in the INCLUDE list or in neither of these lists.
• Statement permissions out of order: When synchronizing the entire database, previous Diff versions would script any database-level permissions prior to any changes in the principals, which could lead to either redundant or incorrect scripts. Fixed.
• Scripting permissions for new principals: when a user or other principal is created or re-created, permissions granted to that user must be scripted as well. Previous versions of Diff didn't script these permissions, this version does.
• GRANT CONNECT not required: in SQL2005+, each newly created principal has a CONNECT permission to the database, unless it is explicitly created using the "WITHOUT LOGIN" option. Previous Diff versions scripted the CONNECT along with any other permissions, which was redundant, even though not an error. This Diff version treats GRANT CONNECT as a special case and only scripts it when needed.
• CREATE USER vs. sp_adduser distinction:Starting from SQL2005, creating a user with 'exec sp_adduser' and with 'create user' produces slightly different results, because 'sp_adduser' creates an implicit named schema as well. In SQL2000 and older, there was no 'create user' statement and no notion of named schemas. When comparing an SQL2000 database (with users defined by 'sp_adduser') against an SQL2005/8 one with users defined by 'CREATE USER', previous versions of Diff would see the difference in the underlying named schemas and and mark the users as different. However, in this context the difference does not make sense and is impossible to synchronize. Now the Diff only makes the "sp_adduser" vs. "create user" distinction if both compared databases are 2005 or newer.
• CREATE USER vs. sp_adduser in side-by-side view: in some cases the Diff would show a difference hint "create user vs. sp_adduser", but (confusingly) display "sp_exec" on both sides of the side-by-side view. This happens because of an option in Diff that forces it to use backward-compatible syntax in the side-by-side view (e.g. 'exec sp_addtype' instead of 'CREATE TYPE'). When comparing databases on two different server versions, this can sometimes produce side-by-side visual comparison with fewer "false" differences. However, in situations when both databases are 2005 or above, there is no need to stick to SQL2000 syntax. Now the Diff disregards the "Use compatible syntax" options if servers on both sides understand the new syntax.
• "Smart comparison" of constraint expressions: SQL2000 and SQL2005/8 have different ways to format constraint expressions, for example a DEFAULT formatted as "(1 + 2)" in SQL 2000 would become "((1)+(2))" in SQL2005 or SQL2008. The Diff has an option to ignore such differences in equivalent constraint expressions. However, due to a bug in the lexical comparison algorithm, the Diff still reported differences on some such expressions. Specifically, the comparison didn't work correctly for 'name + number' expressions, such as "@@dbts+(1)". Fixed.
• Conversions for new date/time types: The internal data type compatibility table in the Diff was not properly adjusted for SQL2008's new types such as DATETIMEOFFSET. As the result, when a column type changed between new date/time types such as DATE/TIME/DATETIME2/DATETIMEOFFSET to a different but compatible type (such as DATETIME), the Diff would re-create the column (and copy the data, if any) instead of just using ALTER COLUMN syntax. Fixed.
• Starting Diff with ADO missing: One recent optimization in AdeptSQL Diff was that it loads and locks ADO on startup, so that it need not be done on each schema refresh. The disadvantage, however, was that if ADO couldn't be found or initialized correctly, the Diff would immediately display an error message and exit. In this version, the Diff still tries to load ADO on startup, but ignores any errors that might occur. If there is a problem with ADO, it will be reported when you actually run the comparison, not on startup.
• Ignore INCLUDE() on SQL2000: Indexes with INCLUDE clause were scripted regardless of the target server's version, although the syntax is only supported by SQL2005 and newer servers. In this update, the Diff scripts the INCLUDE commented-out and logs a warning if the target is SQL2000 or below.
• Precision in TIME types: Diff didn't know that new TIME / DATETIME2 / DATETIMEOFFSET types in SQL2008 can have precision specifiers (as in 'TIME(n)', where N is in 0..7 range). It does script the precision now (when it is different from the default value 7). It now also recognizes and synchronizes precision differences in columns of these types.
• DataDiff: reversed colors in HTML/XLS export: the "left-only" and "right-only" colors (normally blue and red) were reversed in the exported document. This error is fixed now.
• Column alignment in DataDiff HTML export: When exporting data comparison results as HTML file, the Diff would right-align all numeric columns. However, in previous versions this alignment was done for the actual column values, but not for the 1st line containing the column names. Now the column alignment in the HTML is specified for entire columns, including both the header line and data. This also leads to a slightly more compact HTML.
• AV changing portrait/landscape in reporting: for longer DataDiff comparison reports, an attempt to change page orientation in the report preview window from partrait to landscape caused an AV. Fixed.

Fixed bugs

• False ROWGUDCOL flags: In Build 90, some changes were made in the way the Diff reads column attributes from the database. The changed code usually worked correctly with all versions of SQL Server, but on SQL Server 2000 some of the columns suddenly appeared with a ROWGUIDCOL attribute. This was caused by SQL Server returning undocumented bits in syscolumns.status for some columns (while not setting the bits for other columns with apparently exactly the same type and attributes). Fixed.
• Resolving constraint name conflicts: suppose there is Table_A in the source database and a different Table_B in the target, both using a constraint with the same, explicitly specified, name. When synchronizing, the Diff would script the 'CREATE Table_A' before 'DROP Table_B', therefore causing conflict between the constraint names. This is now fixed in two different ways: (a) the synchro scripts are arranged so that the DROPs go first, and (b) the Diff also specifically looks into possible constraint name conflicts and renames or removes the conflicting constraints.
• A situation very similar to the one described above is when you add a column with a named constraint to a table, whereas in the target database the same table uses a constraint with this name for another column. Such situations are also correctly resolved now.

Fixed bugs

• Recent updates had an AV on comparison, which happened in certain rare situations involving computed columns. Fixed.
• Scripted USE with the wrong database: there is an option in "Scripting/Schema Level" that forces the Diff to script "USE database_name" at the beginning of each script. It worked fine for CREATE/DROP scripts, but for synchro scripts (where it normally isn't needed anyway) it reversed the scripting direction: e.g. if we compare DB1 against DB2 and wanted to script to DB2, it scripted "USE db1" instead, and vice versa. Fixed.

Changes

• Ignoring triggers and indexes: for a long time, the Diff has had the 2 options in "Options/Scripting/Tables" to include or not to include indexes and triggers with the tables. These options, however, only affected the CREATE scripting, but not synchro scripts. In this version, when you tell the Diff not to include indexes or triggers with a table, it will also ignore them in the synchro scripts. With the "Include triggers/indexes" settings turned OFF, the Diff will behave as follows:

  • In the schema tree, below the table node any changes in triggers or indexes are highlighted as usually, but the changes don't propagate to the table level or the entire schema level.
  • The changes in triggers or indexes remain visible and scriptable from their respective "Summary collections".
  • If any triggers or indexes need to be dropped to resolve some dependencies, they will be dropped regardless of the settings
  • If a table is dropped and re-created, triggers/indexes will not be scripted and therefore will be lost (the Diff will not attempt to restore them from the source database, as it does for permissions/xprops in similar situtations)

Fixed bugs

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

Changes

• The only changes in this version are some minor corrections in the the About box and the Registration dialog. Internally, there are also changes in how the activation keys are handled, but they shouldn't affect you in any way.

Known problems

• All recent Diff versions have been able to run in "portable" mode (without installation), keeping all their settings in ASQLDIFF.INI and taking the activation key from ASQLDIFF.LIC (in the same directory). However, the "new" setup variant for WS2008 always keeps its activation key in the registry, which makes it inconvenient to use in the portable mode. We do plan to fully implement this feature in one of the future updates.

Supported new features of SQL Server 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.

Features of SQL Server 2005/2008 that are NOT supported

Generally, no "new" objects that appeared in SQL 2005 or 2008 are supported or going to be supported in the current Diff 1.XX release or subsequent minor updates. Most such objects have already been implemented in Diff 2.0 and will be available once that new version of Diff is released. Examples of such "new" objects are .NET-related objects (ASSEMBLIES and .NET-defined types, procedures and functions), Service Broker objects (queues, contracts, services, etc) and various encryption-related keys and certificates.

However, whereever a new feature, such as a new column type or table attribute, extends a common schema object and may interfere with the comparisons, we try to implement it as fully as possible in the "minor" Diff 1.XX updates Diff, so that customers who utilize such enhanced functionality in their databases can continue to use the Diff. Specifically:

• Partitions: currently partition information is not loaded from the database. If a table or index is partitioned, the Diff doesn't recognize the difference and wouldn't script the "ON ..." clause. It is possible that some limited support of partitions would appear in a future 1.XX update (e.g. scripting them in table/index definitions, but not treating partitions and partition functions as "first class" schema objects to be compared and scripted). Full support for all partitioning-related object is being implemented in Diff 2.0.
• FILESTREAMs: this version hasn't been tested with SQL2008 filestreams, so it is not known how the use of this feature in your databases would affect your comparisons. We plan to fully support FILESTREAMs in the next 1.XX update.
• SPATIAL INDEXes: this version hasn't been tested with SQL2008 spatial indexes, so it is not known how the use of this feature in your databases would affect your comparisons. In the next 1.XX update we will verify that such indexes are safely ignored and don't affect your comparisons. Full support for this feature will be included in Diff 2.0.
• Service broker: any SB objects are ignored by Diff 1.xx. Full support for all such objects (message queues, contracts, endpoints, routes, etc) has been implemented in Diff 2.0 and will become available whenever that new version is released.
• Keys and certificates: any such objects are ignored by Diff 1.xx. Full support for keys and certificates has been implemented in Diff 2.0 and will become available whenever that new version is released.
• Assemblies and CLR-defined objects: of all the variety of CLR-related schema objects, the current version of Diff only recognizes CLR-defined types and only to the degree necessary to script columns that refer them in table definitions (see above). The Diff has not been extensively tested with other types of CLR-defined objects (procedures, functions, triggers), so it is not known whether they can interfere with its comparison and scripting. We plan to conduct such testing and make sure that Diff 1.XX is aware of such objects and safely ignores them. Full support for .NET assemblies and CLR-defined objects already exists in Diff 2.0 and will become available whenever that new version is released.
• Fulltext search: fulltext indexes and catalogs are ignored in Diff 1.xx. Unfortunately, this might break synchro scripts generated by the Diff when the presence of a fulltext index prevents a table or its columns from being dropped. We might have to add some limited support for text indexes (so that they can be detected as dependencies and dropped/re-created accordingly) in a future 1.XX update. Full support for fulltext catalogs and indexes has been implemented in Diff 2.0 and will become available whenever that new version is released.
• Datamining and "cube" extensions (DMX, MDX): the current version of Diff doesn't support them and so far we don't plan to support them in Diff 2.0 either.

Changes not specific to SQL2008

• ANSI_PADDING compared: behaviour of character and binary columns may be affected by the ANSI_PADDING setting during the table's creation. The server keeps the state of this setting with each column, so it actually becomes one of column attributes and as such should be compared and scripted. Before this version, the Diff ignored the ANSI_PADDING attribute, now it loads it and can (optionally) compare and script it. A new checkbox "Compare ANSI_PADDING" has been added to the 'Scripting/Tables' options page. This option is OFF by default, so the Diff will continue to ignore the ANSI_PADDING attribute until you instruct it otherwise. Even when ANSI_PADDING comparison is enabled, the Diff won't be able to script the situation when individual columns within one table are created with different ANSI_PADDING. Instead, a warning will be issues and the entire table scripted with ANSI_PADDING ON.
• Matching "principals": We changed the way database principals are compared. Although all such objects are listed under the same node of the schema tree, previous versions of Diff would only match user vs. user, role vs. role, approle vs. approle. In this version, principals with the same name but having different type (such as user 'tester' vs. role 'tester') will be paired.

Fixed bugs

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

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

Fixed bugs

• 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.
• In previous versions, when changing type of a principal (e.g. from user to app role), all objects owned by this principal were moved to DBO. This (a) created possibility of a name conflict in DBO namespace and (b) the Diff should then move the objects back to the re-created principal, which it didn't. The correct solution would be to move the objects into a temporary schema (or app role), then move back to the re-created owner.
• 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.

Bug fixes

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

Changes

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

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

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

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

Known issues

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

Bug fixes

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

Bug fixes

• The PERSISTED attribute in computed columns was ignored. In this version the attribute is correctly obtained from the database, compared and scripted. The Diff now uses 'ALTER TABLE ... ALTER COLUMN ... ADD|DROP COMPUTED' when appropriate.
• NOT NULL on PERSISTED columns: In SQL2005, computed columns with the PERSISTED attribute are exactly like any regular column in that they can have the NOT NULL attribute and any kinds of constraints normal columns could. This functionality has been added.
• Names with leading dot(s): The Diff generated an error on procedure definitions like 'CREATE PROC .ProcName'. Having the leading '.' in object names turns out to be a valid syntax (and some of MS corporate solutions happen to have a lot of such notation in their DBs). Now the new version happily eats away any valid combination of dots and names. However, it will NOT use the same notation when scripting, so any leading dots in procedure names will be lost after synchronization. Objects with and without the '.' prefix in the object name in CREATE PROC|VIEW|etc ... (but otherwise identical) are treated as identical. The '.' prefixes inside object bodies are compared and scripted 'as is'.
• When a computed column's expression with something like '...CHAR(13)...' is begin ported from SQL2000 to SQL2005, the SQL2005 server saves it as '...CHAR((13))...' (with an extra level of parentheses added). When re-comparing, the Diff sees that as a difference and so tries to synchronize the computed column again. The "Smart comparison" option (which was added specifically to handle such situations) didn't worked. Fixed.
• Renaming issues: Renaming PK/UQ constraints, indexes and statistics could cause name conflicts, because indexes and statistics share the same namespace and the PK/UQ constraints rely on indexes with the same name. Besides, both PK/UQ and indexes can be CLUSTERED and there can be only one clustered index/constraint per table. Therefore, before renaming or adding a specific object, the Diff must find and either rename out of the way or drop any conflicting ones in the target database. This functionality has existed in previous versions, but we found several situations where it didn't see a conflict and the produced scripts were incorrect. Fixed.
• If there are several indexes to create, the clustered one (or the clustered constraint) should be created first, to avoid re-building nonclustered indexes when a clustered index is created). Done.

Known problems

• It seems that when a user cancels a DataDiff comparison (e.g. because the datasets are too large), the program leaves some (or both) of the SELECT statements in a 'suspended' state, even after the user proceeds to compare some totally different databases. These open connections (and locks) can lead to problems if some other client attempts to access that table. The issue is NOT solved in this version, but a workaround is simple: exit and restart the Diff after a cancelled comparison to make sure all connections are closed.

Bug fixes

• Synchronizing clustered indexes with different names and column lists, the Diff tried to create the new index before dropping the old one. Since a table can only have one clustered index, the synchro script failed. A similar error happened when a table contained a clustered index and its pair table had a clustered PK or unique constraint. In this version, the Diff checks all such situations and makes sure to drop the clustered index / constraint, if any, before creating a new one.
• On some machines, a database scan occasionally failed during the "Reading AUTHORIZATION overrides" step. This intermittent error appears to be caused by the fact that during the step the Diff created a new SQL query before closing the previous one, so that ADO had to allocate one extra connection and on some systems that probably was one connection too many. In this version, the "Reading AUTHORIZATION" step is done without creating an extra connection, so that should solve the problem.

Bug fixes

• Missing port number connecting to remote server. When connecting to a remote SQL server via TCP/IP, the OLEDB driver expects to find both the IP address and the port number in the connection string, whereas the Diff incorrectly assumes that the "standard" SQL port 1433 will be used by default and so doesn't include the port number 1433 (although it does include any other port number). Fixed.
• There is a "Digest threshold" option in "Options/Data Comparison/General" which is supposed to be just a default, overridable for any particular column. However, the DataDiff incorrectly used this global setting even if a column-level override was specified. Fixed.
• "Digested" GUIDs in DataDiff: if a "digested" BLOB column is immediately followed by a UNIQUEIDENTIFIER column, the GUID is also displayed (incorrectly) as a "digested" value. This happened either when the two columns are adjacent on the same row, or when a "digested" value is the right-most column and the GUID column it goes first on the next row. Fixed.
• Error reading NULL value in a GUID column. Reading columns of type UNIQUEIDENTIFIER, the DataDiff didn't check for NULL value and any such value would cause a variant conversion error, breaking the data comparison. Fixed.

GUI improvements

• When you are trying to INSERT a row with "digested" BLOB(s), the DataDiff (correctly) refuses to script it. However, the explanation message goes into the main form's error log panel and isn't shown in the pop-up error msgbox. If the DataDiff window is maximized or otherwise obscures the error log panel, you won't see what's actually happened, which is confusing. The correct solution would be to give DataDiff its own error log panel, meanwhile the popup message has been changed to tell you where to look.

Known problems

• Failed test with re-creating "user" as "approle", etc. Objects owned by this user are re-owned to "dbo", but aren't re-owned back once the user is re-created.

Bug fixes

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

• AdeptSQL Diff now fully supports XML schema collections in SQL2005. We didn't plan to add this feature before Diff 2.0, however the absense of XML schemas made it difficult to add or synchronize typed XML columns.

Bug fixes

• Fixed the "Permission #0 not expected here" error, which sometimes occured when reading database-level permissions in SQL 2000. This was caused by an undocumented permission code which, apparently, MS uses in some of their ecommerce solutions. The undocumented permission code doesn't seem to correspond to any database-level permission you can explicitly specify, so the new Diff simply ignores it.
• DataDiff corrections: The server-side DataDiff synchronization between typed XML columns (those with XML schema collection) caused server errors, because in this situation the server wants to see an explicit type conversion. Fixed.
• When comparing columns of different SQL types, DataDiff didn't report them in the column configuration dialog as having different types (e.g. "uniqueidentifier/varchar(36)"), but only displayed the type of the left-hand column. Fixed.
• Comparing a UNIQUEIDENTIFIER column against a VARCHAR column (with the same GUID converted to a string) produced a mismatch, because DataDiff formatted GUIDs with surrounding '{}', whereas SQL conversion of a GUID to a string doesn't include the brackets. Fixed (by removing the brackets).
• Error reporting: When incorrect connection parameters (e.g. a wrong password) were specified for one of the databases, the Diff would report the error, but would not tell in which database it happened. This version does specify "Left-hand DB" or "Right-hand DB" in the error message.
• In the same situation as above (wrong connection parameters or other error during the schema scan), each error was reported twice. Fixed.
• When the Diff was configured to ask for confirmation before refreshing one of the schemas (after applying a change script to it), it always displayed the name of the right-hand schema in the confirmation dialog, regardless of which schema is in fact going to be reloaded (although it did reload the correct side of the comparison). Fixed.
• After you've executed an update script in Diff's SQL editor, the Diff remembers that the schema might have been changed and need be reloaded. When you are closing the editor window, the Diff would ask if you want to reload the schema. However, if you manually re-load the schemas while the SQL editor is open, the Diff doesn't reset the "dirty" flags and still thinks it needs a reload. Fixed.
• Primary keys with different key columns are correctly displayed as changed and correctly synchronized, however there was no "tooltip" description for the change (i.e. when you place the cursor over the changed PK). Fixed.

Bug fixes

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

Bug fixes

• Changes in PK columns were ignored by the Diff. Fixed.
• Modifying table columns referred by a computed column would sometimes cause an exception. Fixed.
• Permissions: when revoking a permission WITH GRANT OPTION, the Diff didn't script the CASCADE clause, which is required in this case. Fixed.
• Permissions: when there are column-level permissions in the source DB, but only object-level permissions in the target, an attempt to script the difference would cause an exception. Fixed.

Known problems

• An exception has been reported after executing SQL in the built-in editor. It is likely to be caused by a particular SQL script and hasn't been reproduced yet.
• An assertion error has been reported loading permissions. We made a SQL script which enumerated all valid combinations of database-, object- and column level permissions. The script generated over 150 individual permission-setting tests for SQL 2005 and 28 tests for SQL 7.0/2000. The Diff has successfully passed all these tests, so that didn't help us to reproduce the problem. Some permission-related corrections have been made as well, but it is not known whether this particular assertion error got fixed or not.

Changes

• The help file has been updated.
• When the Diff syncronized (added) filegroups, it didn't add files to them. This was done by design, kind of, because file paths on the target database would certainly be different from those in the source DB. In both SQL2000 and SQL2005 (but not on SQL 7.0), the server would even allow tables and indexes to be created on such an empty filegroup. However, some files must be added before the tables could be populated with data, which means that in such situation the Diff leaved the database non-functional, with a difficult to find problem inside. In this version, the Diff extracts the default data file path of each database (that is, where its PRIMARY filegroup resides) and will create paths for the new files from that. More than that, it checks if a particular file name is already in use in the target database and modifies it with a unique numeric suffix, if necessary.
• For an unchanged item selected in the schema tree, the Diff used to replace two separate menu commands "Script CREATE from left DB"/"...from right DB" with a single "Script CREATE" command, on the assumption that the two scripts would be identical anyway. However, starting from version 1.95, the Diff can be configured to ignore certain differences. So even if two items appear as identical in the schema tree, CREATE scripts for them may be different. Because of this, the UI logic has been changed to always keep two distinct "Script CREATE from ..." commands.
• In SQL Server versions prior to SQL2005, objects like tables and views were 'owned' by a user, group or role. In SQL2005, such objects belong to a specific named schema instead, and normally the owner of the schema also owns all objects inside the schema. However, it is still possible to have a separate owner for an object (using "ALTER AUTHORIZATION..." SQL statement). Before this version, the Diff didn't detect such explicit authorizations for system objects, so it couldn't resolve all dependencies when dropping a USER having owned objects. This is done now.
• A user has reported that the SQL server sometimes glitches when several different constraints are all added in the same ALTER TABLE... statement, whereas the same sequences of changes in separate batches works just fine. To solve the problem, a new option "[x] Don't group changes in ALTER TABLE..." has been added to Options/Scripting/Table. It is initially turned off, not to change the usual Diff behaviour, but if you check it, the Diff will issue separate statements for each column or constraint being added to the table.

Bug fixes

• When making a CREATE script (not a synchro script) for filegroups, the Diff used to script the [PRIMARY] group as ALTER DATABASE ... ADD FILEGROUP, which was an error (as the PRIMARY filegroup is always created with the database). This version never scripts [PRIMARY], and it only scripts ADD FILE if there are additional files in the PRIMARY (skips the first file).
• "Can't focus on a disabled window" error on the connection panel: when both databases are on the same server, the connection parameters are not complete and you try to choose a database name on the right-side panel, the Diff shows a message box ("Connection parameters not complete"), then tries to set focus to where it thinks you should enter the missing parameters. However, it didn't realize that both DBs were controlled from the left-side panel and so tried to focus on a disabled field in the right-hand box, causing the error. Fixed.
• When the Diff synchronized a table by re-creating it and copying the original data, it incorrectly includes TIMESTAMPs into the list of copied columns. The server does not allow to change TIMESTAMP columns, so this version leaves them alone.
• The Diff didn't correctly script various situations with a UDT having a global DEFAULT or RULE bound to it, and then overriden or un-bound for a particular column with this UDT. Such changes are correctly scripted now.
• The Diff would compare a USER against an APPLICATION ROLE with the same name, causing an assertion error (because users and app. roles are internally represented by objects of different classes and the Diff is not supposed to compare such). In this version, the Diff would never associate USERs, ROLEs and APPLICATION ROLEs with each other, even if they have identical names. This might not be a perfect solution, but it doesn't look like it might cause any new problems or inconsistencies.
• Must resolve name conflicts btw USER/APPROLE/ROLE, which are all in one namespace, but might not be associated with each other (e.g. if the script creates an app role, user with the same name must be dropped first). Done.
• The Diff didn't correctly script changes between a 'pure' SCHEMA and an old-style USER+SCHEMA combination (with the same name). Fixed.
• In DataDiff reporting: after editing / saving a report template, the Diff shows "Duplicate name" error if you try to run the new report. Fixed.
• The connection panel didn't resize well under some non-standard screen resolutions (comboboxes overlapping). The problem haven't been reproduced here, but some changes in this version should prevent this from happening.
• Constraint syntax "DEFAULT (expr) FOR (column)" is valid for ALTER TABLE, but not for CREATE TABLE. The Diff knows that and, accordingly, doesn't allow the "At table level" setting for DEFAULT constraints (in Options/Scripting/Tables/Constraints). However, if such option was set by manipulating INI file or registry, the Diff didn't pay attention and would generate invalid code. Fixed.

Bug fixes

• Under certain conditions, when scripting the entire database, FK were scripted twice. Fixed.
• The Diff would sometimes attempt to script a multiple-column FK as a column constraint. Fixed.
• When reading schema from SQL2000, with loading restricted to a particular owner, the Diff would break on user-defined types. Fixed.
• Values of extended properties and names of some objects were loaded without taking into account the selected code page. As the result, national characters there were loaded incorrectly or replaced by '?'. Fixed.
• The code page selected in "Tools/Choose encoding" wasn't always saved on exit. Fixed.

Changes

• Generating "sp_addtype" in SQL2000, the Diff always added the owner name as the 4-th parameter. This is not an error, but it is usually redundant, since most UDTs are owned by DBO anyway. This version only scripts the owner parameter when it is actually needed.
• The Diff has ways to check if any prerequisite objects are missing from the script. Then it inserts that "Might need to be edited" comment into the script. However, it did so even when preparing a script for side-by-side comparison, where no dependencies need to be resolved and such comments were only distracting. The dependency-checking logic is now bypassed for the "visual" scripts.
• The SQL editor window now has a status bar indicating the line/column numbers of the cursor position. It also has a "maximize" button on the caption.

Changes and improvements

• Improvements in handling the "EXEC AS ..." clause: where the previous version just used the textual data extracted from procedure body (self/caller/owner/'user_name'), this version actually keeps a link to the corresponding "user" object. This ensures that the Diff now correctly handles "exec as ..." referring to renamed users, understands that "exec as 'dbo'" can be equivalent to "exec as self", ignores 'exec as caller' and so on.
• The ability to compare "EXEC AS..." clauses literally remains as an option controlled from "Options/Script/Procedures", although it is not quite clear anybody would need it. By default the option is OFF, so the Diff does the "smart" comparison as described above.

Bug fixes

• Fixed an occasional AV exception filling the schema tree (after loading from the databases).
• The fixed role [public] was not loaded. Accordingly, any permissions assigned to that role couldn't be compared and scripted. Fixed.
• Prerequisites are scripted for ALTER ...: When you select a particular object in the schema tree and script it, the Diff makes sure to script any prerequisite objects first. E.g. to script a table [User1].[MyTable], it checks if [User1] should be scripted first. This feature, however, didn't work when ALTER'ing procedures and other code objects. E.g. before scripting "ALTER PROC P WITH EXEC AS 'User1' ...", the Diff should have make sure that [User1] exists in target database or has been already scripted. The new version handles this situation correctly.
• "EXEC AS" dependencies: When you have a procedure with "EXEC AS user_name" clause, that user can't be dropped without removing the reference from the procedure first. This version of Diff tracks this dependency.
• "Owner mapping" for "create user": The owner mapping didn't work when the users in question were created without their implicit schemas (i.e. by "create user" rather than by "sp_adduser"). Fixed.
• XProps extended: SQL2005 allows to attach axtended properties to a wider variety of objects then SQL2000. Some of these new XProps were not properly compared and scripted. This version additionally supports XProps on named schemas, file groups and individual files.

Bug fixes

• Assertion error comparing SQL users: Comparing users produced an assertion error when a user with the same name was defined as SQL Server login in one DB and as Windows login in the other. Fixed.
• Login names were not loaded for users created from Windows users or groups. Fixed.
• Outdated "sp_adduser/sp_dropuser" calls have been replaced by "sp_grantdbaccess/sp_revokedbaccess".
• The "... WITH EXECUTE AS ..." clauses for procedures and other code objects was not loaded from the database. Fixed.

Additions and changes

• This version has an option not to script database names in the side-by-side view. Very often, the two compared databases have different names and there is no need to synchronize these names. However, any "alter database" or "sp_dboption" includes a particular database name and thus shows up as a difference in the side-by-side view. To reduce the number of such diffferences, the Diff now shows "<database_name>" placeholder instead of the actual database names. This new feature only affects the SQL shown in the side-by-side view, but not any "real" scripts nor changes shown in the schema tree. It can be turned off from the "Options/Scripting/Schema level" options page.
• Some changes ahve been made in the way the separator comments are scripted in the side-by-side view (see comments to B73 for more details).
• A number of minor improvements has been made in the way users / roles are compared and synchronized.
• Removed an an unused setting "use sp_dboption vs. alter schema" in "Options/Scripting/Schema level".

Bug fixes

• STATISTICS automatically created by the server need not be loaded and compared. The previous version correctly ignored auto stats on SQL2005, but loaded them from SQL 2000 (possibly causing a lot of false differences). This version does filter out automatic statistics on both SQL2000 and SQL2005.
• One useful option recently introduced in the Diff is not to synchronize users/roles from the schema level. However, in SQL2005, each user is granted a CONNECT permission (on the schema level) and those permissions did get compared regardless of the "Ignore users" setting, resulting in false (and unsynchronizable) differences on the schema level. Fixed.

Additions and changes

• The Diff now displays and synchronizes named schemas in SQL2005. Note that "implicit" schemas (those created automatically by the server when calling "sp_adduser") are normally not shown as a separate entity. The Diff now understands the difference between "exec sp_adduser" (which remains in SQL2005 for backward compatibility and creates both a user and its implicit schema) and the new "CREATE USER" syntax, which only creates a user but not a schema. The Diff can script using either syntax, depending on what is required. It can also synchronize old style and new style users by adding or dropping the associated schema.
• Full 'CREATE USER/ROLE' support in SQL2005. The Diff recognizes users created from CERTIFICATEs or ASYMMETRIC KEY, users with or without login, WITH DEFAULT_SCHEMA, as well as AUTHORIZATION clause for roles. Note that the Diff now loads names of CERTIFICATEs or ASYMMETRIC KEYs used with the users, it still does not keep these objects as part of its schema model and this is not expected to happen before version 2.0.
• When the built-in users/roles (dbo, guest, etc) are scripted for the side-by-side view, the Diff now includes a comment saying that the SQL will not be generated in the actual script. When you try to script these users/roles, the Diff would display a warning and ignore them.
• Comments improve side-by-side comparison: When SQL representations of two tables are compared side-by-side, one of the tables might have, for example, a lot of indexes, whereas for the other one constraints or extended properties migh be scripted on the same line numbers. The side-by-side comparison algorithm is strictly textual and knows nothing about SQL, so it can easily get confused and "match the unmatchable". To avoid this, the Diff now inserts into the side-by-side scripts special marker comments which would help to synchronize the side-by-side view.
• There was that obscure "LineWeights" page in the "Side-by-Side Options" dialog. The option page has been removed, instead a list of "line weights" suitable for SQL comparison is now stored in a configuration file "asqldiff.lws" distributed with the program (you can find more details inside that text file). Specifically, line weights are defined for each of the comments mentioned above.
• Added support for the RECOVERY MODEL database property.

Bug fixes

• There was a combination of errors reading schema with system tables: the Diff could not process columns for system tables. It then tried to report the error, but failed to synchronize between the reader thread and the GUI thread, ended up with a "window device context" exception. This version fixes both of these problems.
• When you make the Diff to scan a single database and then script a table, the Diff sees there is no database on the other side, and stupidly tries to add a [PRIMARY] filegroup first. Fixed.

Bug fixes

• Fixed an "assertion failure in AltContext.pas" when a change involved re-creating dependent views.
• Scripting a global DEFAULT or RULE with an extra-long (>4Kb) expression might produce a buffer overrun and an AV. Fixed.
• When dropping column, any STATISTICS dependent on it are also dropped. However, the Diff tried to re-create that STATISTICS, although their column already their column already wasn't there. Fixed.
• A hidden TEXTIMAGE_ON difference: If a table originally had contained a TEXT or other BLOB column, then the column was removed, the server does not remove the associated filegroup record. If the matched table in the other database is identical, but never contained a text column, the Diff would report a (false) difference in the TEXTIMAGE_ON filegroup. This version doesn't consider it a difference when a [PRIMARY] filegroup in one table is compared against a NULL filegroup in the other one.
• The new SQL2005-specific syntax for users and roles, introduced in the last update, has been temporary disabled again. Apparently, SQL2005 handles the old-style 'sp_adduser/sp_dropuser' differently than the new 'create user/drop user': the old form automatically creates or drops a 'default schema' for the user, the new form creates/drops only the user, but not the schema. E.g. it is impossible to use 'drop user' on a user created with 'sp_adduser', as the associated schema must be explicitly dropped first. Some more thinking, coding and testing is necessary to re-enable this new feature.

Additions and changes

• Now if the "target" database is SQL2005, then users, roles and application roles are be scripted using new "CREATE/DROP USER/ROLE/APP.ROLE" syntax instead of the old "exec sp_adduser...", etc. procedure calls.

Bug fixes

• Database-level and object-level permissions new in SQL2005 have been tested and several critical issues fixed (including an "Invalid typecast" exception when reading permissions attached to users or roles).
• GRANT/DENY in SQL2005 requires any securable entities other than 'sysobjects' to be prefixed with that '::' qualifier (e.g. "TYPE::MyInt"). The previous version didn't do that, B71 does.
• SQL2005 does not accept "REVOKE ALL" for some entities, requiring an explicit list of permissions to be revoked. The new version does build such a list.

Additions and changes

• STATISTICS on SQL2005: STATISTICS now implemented on all supported versions of MS SQL Server (7.0, 2000, 2005).
• Added a new option page for STATISTICS. They can be totally ignored, loaded but not synchronized (retained as they were, re-creating them when necessary) or compared like other objects. An additional option allows to ignore STATISTICS names and compare them based on their column lists only.
• NOTE that the default behaviour for the Diff is now to load STATISTICS (so that it will know to DROP-script them, whenever it is necessary to sync columns) but not to sync them. If you need the STATISTICS to actually be synchronized, change the comparison mode in the new option page described above.
• The logic of Ignore/Retain/Compare for extended properties is now fully implemented and tested.
• Added comparison and scripting of 'role membership' for users and other security accounts.

Bug fixes

• Fixed that AV on loading schema (the one with "TSchCollection.NormalizeName" in the stack trace).
• At some point around Build 65, the Diff lost the ability to refresh views and functions when tables they depend upon are changed. This functionality has been restored in this version.
• In the window caption and in the popup hint of DataDiff, the program displays names of the two tables being compared, including, as necessary, server and database names to make it clear which table is where. In some situations, however, the difference was not obvious and the caption could look confusing. In this version, the logic has been changed to ensure that (a) if the compared databases are on two different servers, the caption will always show that and (b) if table or database names are only differ in capitalization, the Diff will find some other part of the name to make them look distinct.

Additions and changes

• Permissions:Comparison of object-level and database-level permissions has been rewritten. The Diff now recognizes all new types of permissions introduced in SQL2005 on database level, and object-level permissions for all 'securable' objects it currently support (including types, users, procedures, etc.) and not just tables as it was before).
• The option to ignore/retain/synchronize permissions is now fully implemented. There is also a new option to 'optimize' permissions, which means the Diff would ignore any redundant permissions both on comparison and when scripting. Currently the detected redundancies include: (a) granting/denying on column level what is already granted/denied on object level, (b) granting a particular kind of access (e.g. SELECT) when it is already implied (e.g. by granted CONTROL).
• The Diff now supports (loads, compares and scripts) the STATISTICS objects in SQL 2000 and SQL 7.0. They are still not supported in SQL2005, though.

Bug fixes

• The previous version sometimes showed apparently identical PK and UNIQUE constraints as different. These false differences in PKs were likely to be due to different default 'fill factor' settings on the two servers, which would make its way into the indexes underlying the constraints. It was properly filtered out for regular indexes, but showed up in the constraints. This version fixes this particular 'fill factor' issue, but whether it completely eliminates the false differences in PKs remains to be seen.
• An access violation loading the schema was reported by several users but could not be reproduced here. It might have something to do with loading of permissions, in which case it is probably fixed in this version.
• Various situations involving a global DEFAULT or RULE bound to a column via a user defined type were not correctly scripted. E.g. altering an existing column to a UDT-with-default does not automatically bind the default, whereas for a new column it does. There are other similar complications as well. About 10 such situations are now added to the test set and this version of Diff correctly handles them all.
• Due to some recent changes in Diff's internal schema model, the table name was missing in the ALTER TRIGGER statement. Fixed.

Additions and changes

• Displaying difference details: For each compared schema object, the Diff now keeps a detailed bit mask of changes and can display the list of changes in user-readable form. This list pops up as a 'tooltip' while you move the cursor over the schema tree.
• The same difference details are also shown in the 'Details' column of comparison reports.
• The Diff now correctly loads, compares and displays FILEGROUPS for tables and indexes, in both SQL2000 and SQL2005. Note that this feature is controlled by the "[x] Ignore filegroups" checkbox on the "Comparison/Ignored details" option page, so if you need the filegroups, make sure the checkbox is cleared. NOTE: the "ON partition(column)" syntax of SQL2005 is not supported yet!
• Faster schema loading: The schema loading has been optimized in terms of both complexity of the queries (less JOINs now) and the amount of metadata transferred from the server.
• The option in "Selective loading" which controlled whether or not tables should be checked for the presence of data has been removed. The Diff now uses a much more efficient way to obtain record counts for all loaded tables, so it always gets them. It is still possible to script as if all tables had data, but the option is now a checkbox in "Scripting/Tables".
• Ignoring differences. Usually, if there is a change in some schema object (so that it shows black in the schema tree and get synchronized), it propagates all the way up to the schema level. Now the Diff has a mechanism to isolate/ignore certain changes. For example, if you tell it not to script users with the schema (see "Options/Scripting/Schema level"), you will still see the changes under the Users node, but those changes wouldn't be scripted from the schema level and, provided that there are no changes in other objects, the root node will appear unchanged.
• The dependency tracking has been improved. In particular, scripting of FOREIGN KEYs is now properly deferred until all required items are scripted first. Several other 'tricky' dependency situations have been resolved as well:
• Computed columns must be removed (and later re-created) before changing data columns they depend upon. The Diff now detects such dependencies and correctly scripts them.
• Consider a situation when a view X in the target database prevents adding table X from the source database. Such conflicts may occur between any two top-level entities or between PK/UQ constraints and indexes in the same table. The Diff now correctly resolves these name conflicts, removing or renaming the conflicting object.
• Smarter comparison for indexes and constraints: the Diff now uses more complex rules to tell whether two indexes are 'equivalent' and should be paired in the schema tree. For example, primary XML indexes for for same column are always equivalent, regardless of their names. In other situations, identical lists of key columns and identical UNIQUE/CLUSTERED attributes are taken into account.
• SP checksum matching: There is now an option (in "Options/Comparison/Code Comparison")to consider code objects (procs, views, functions and triggers) equivalent if they have identical bodies, even though their names don't match.
• XML indexes were ignored by previous versions of Diff. They are fully supported now, including loading, comparison and scripting (SQL Server 2005).
• Added support for non-key columns (the INCLUDE clause) in indexes (SQL Server 2005).
• An option to script an "IF [NOT] EXISTS(...)" conditional statement before DROP/CREATE has existed in the Diff for some time (see Options/Scripting/Tables), but it only affected scripting of CREATE TABLE. Now this conditional logic works for most DROP statements (for all sysobjects, indexes and UDTs) and for CREATE statements, whereever SQL Server permits it (that is, for tables, constraints, global rules and defaults, UDTs and indexes).

Bugs fixed

• All new and changed functionality has been tested on MS SQL 7.0, 2000 and 2005. A number of incompatibilities have been found and fixed for MS SQL 7.0
• In previous versions, DataDiff incorrectly treated rows like (1,NULL,2) and (1,2,NULL) (same sequence of non-null values, but in different columns) as identical, because the fast checksum-based comparison didn't take the NULLs into account. This is fixed now.
• Synchronizing columns with COLLATE clause, the Diff would sometimes place COLLATE inside the CONVERT() call, which is not a valid syntax. The right thing is to script such conversions as "CONVERT(...) COLLATE ...", which is how the Diff does it now.
• The Diff now doesn't script BEGIN TRAN/COMMIT if there would be only one SQL statement wrapped inside the transaction.
• There was a well hidden error that caused the schema tree to become un-sorted in some updates. The problem had seemed to be fixed, but then appeared again. Now it has been fixed for good.

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.

Changes and improvements

• Redesigned the options controlling name comparison for constraints. There used to be two separate settings "Ignore constraint names" and "Script automatic constraint names", with some of the combinations confusing or even mutually exclusive. Now it has become one setting with three "levels" of constraint name comparison and scripting (All ignored/only named/all compared).
• The schema tree now displays additional details for some of the objects: types for columns, values for extended properties, etc.

Bug fixes

• Improved table scripting: Many corrections are done in the ways the Diff synchronizes table columns of different types, with or without data, with or without re-creating the particular columns or the entire table, explicitly converting compatible column data when necessary, providing temporary DEFAULTs for added NOT NULL columns, handling possible data truncation, etc, etc.
• Better dependency tracking: the Diff now delays scripting of foreign keys until both the refering and the referred tables are defined. In some other situations it also forces "prerequisite" objects to be scripted first, although the dependency resolution is not yet 100% reliable.
• Numbered procedures: When reading a group of numbered procedures, Diff was one-off associating the procedures with their numbers. Multiple corrections are also made in the way the Diff creates and removes numbered SPs (drops the whole group, then re-creates any SPs remaining in the group).
• Delimited names in sp_rename: In some situation while temporary renaming a column, the Diff incorrectly used delimited name (like [this column]) in a call to sp_rename, which was wrong: the delimiters would end up as a part of the new name. Fixed.
• Recreating the only column in a table: When Diff tried to drop and recreate a column, and that column happened to be the only one in the table, ALTER TABLE DROP COLUMN would fail. This is now fixed by temporarily adding (and then removing) a dummy column.
• A number of corrections has been made in loading, comparing and scripting of extended properties. Now they work correctly for all schema objects in SQL2000 and for all supported schema objects in SQL2005.
• A number of scenarios involving changes in user-defined types (UDTs) have been tested and appropriate adjustments made in the scripting engine. Now the Diff correctly supports situations when a change in a UDT also involves changes in dependent columns, when a column changes from a UDT to a native type and back, in a table with or without data, etc.
• Various situations have been tested involving either synchronizing the NOT FOR REPLICATION attribute (for IDENTITY columns, for triggers and for constraints) or retaining it while synchronizing other changes in these objects (if the "Ignore NFR" option is set). A number of corrections have been made.
• There are certain column attributes (IDENTITY, ROWGUIDCOL and TIMESTAMP) that the server only allows for a single column in a table. When moving any of these attributes "upward" from one column to another, the attribute should be first removed from the original column. The Diff now takes care of that.
• Exrta-large expressions: The Diff didn't expect any expressions (e.g. in a computed column) to be longer than 4K (the size fitting into a single [syscomments] record). However, such long expressions are possible (e.g. with a long binary literal or with a long CASE...) and they would cause error loading the schema. Fixed.
• A number of corrections is done in reading, comparing and scripting global DEFAULTs and RULEs and their bindings to specific columns and types. Among other things, the Diff now recognizes equivalence between bound RULEs and regular CHECK constraints, as well as between bound global and in-table DEFAULTs. An option has been added to ignore the way these constraints are implemented (in-table or bound) as long as the actual check expression or default value are identical.
• Resolving name conflicts: The Diff didn't correctly track situations where each of the two databases uses the same name for a different kind of objects, e.g. RecentCustomers can be a table in one DB and a view in another. Trying to sync leads to a name conflict: e.g. the view has to be dropped before the table can be added! This version of Diff is aware of this situation and drops the conflicting objects when necessary.
• Mapping owner names: It has been possible for some time to specify "owner mapping" in the Diff and thus compare [X].[object1] in one database against [Y].[object1] in the other (where X and Y are owner names). However, this implies that the Diff should have used owner names from the target database (that is, "Y" is this example) whenever it needs to modify or add schema objects. In previous versions, this was not done consistently for all kinds of generated scripts. Fixed.
• Some corrections scripting DB properties: (a) they were scripted with the wrong database name (the source, rather than the target) and (b) Database properties can not be scripted inside a transaction. Now the Diff commits an open transaction, if any, before scripting the properties.

Known problems

• Comparison and scripting of schema permissions (both object-level and statement-level) still needs some work. Right now, the Diff allows the choice between not loading permissions at all (and thus losing permissions whenever any objects in the target database are recreated) and loading, comparing and scripting them, which is likely to either fail (if there is no such user in the target database) or produce undesirable results (permissions dropped or created according to those in the source DB). It seems that the typical situation is to have different sets of users and permissions on a test DB and on a production one. Accordingly, the default mode should be to load all the permissions, ignore them in the comparison, then re-create them as they were whenever an object in the target database is re-created.

Changes and improvements

• DataDiff column-level scripting options have been redesigned to be easier to use and less ambiguous: arranged into "Left" and "Right" groups as they were, it wasn't clear which group of options takes effect when synchronizing from left to right or the other way round. Also the "Automatic" scripting mode (default) has been added, which means the Diff is free to choose the best scripting mode for that column and does not get stuck in the chosen mode when conditions change (e.g. when the column status is switched between 'compared' and 'ignored').
• DataDiff: Whitespace changes in column values were not visible in the data grid. This version allows to display spaces as dots. Note that this option (in "Schema Options/Data comparison/General") is initially disabled, so yo won't see ths new feature unless you turn it on.
• Preference settings (in "Data comparison/Large data") have been added to control the types of columns server-side scripting is preferred for.

Bug fixes

• There can be a situation when compared foreign keys refer to the tables which are matched to each other but have different names (e.g. "references dbo.my_table(x)" vs. "references user1.my_table(x)", where owner names [dbo] and [user1] are equivalent according to the "Owner mapping"). The previous versions of Diff would not recognize these FKs as identical and would try to synchronize them. This is fixed now.
• In the DataDiff data grid, when you sort it by one of the (numeric) columns, NULL values in this column not only don't get sorted correctly, but also disrupt ordering of the neighbouring rows. Fixed: now NULL is always considered smaller than any non-null value (even 0 or empty string).
• The 'trim trailing spaces' column comparison option in DataDiff didn't work. Fixed.

Feature requests

• The code to re-arrange the order of scripted objects according to dependencies between them in still under construction.
• Need a dialog displaying the list of additional objects to script as dependencies, a stored option to control the default behaviour (re-arrange Y/N, extra objects Y/N/Confirm)
• The Diff should be able to delay scripting of FKs whose target table is not yet defined. This might be needed even for re-arranged tables in case of circular dependencies.

Changes and improvements

• Table data compared in the DataDiff viewer can now be saved into a file or copied into clipboard in one of two major formats: as HTML tables or as MS Excel XML spreadsheets. In both formats, the color highlighting and text styles for changed/unchanged columns are properly preserved. This new functionality of DataDiff is fully Unicode-enabled (the data in exported files is saved in UTF8).
• When you specified upper/lowercase conversion for SQL keywords (in Options/Script/Formatting), the setting also affected predefined data types such as INT or CHAR. However, data type names in MS SQL are identifiers rather than reserved keyword, so strictly speaking that was incorrect. This version allows to set the case conversions separately for keywords, identifiers and known data types (see the Script/Formatting option page). Note that user-defined types are handled as identifiers anyway, because the formatting code uses a fixed lookup table (with type names like 'INT', 'VARCHAR', etc).

Bug fixes

• In DataDiff: you could specify the digest threshold for BLOB values of upto 64KB, but if you actually set it above 32KB, you could get an assertion failure. Fixed: DataDiff now consistently uses 64KB as the limit.
• Scripting: when (a) a column type changes, and (b) the column has a default on both sides, and (c) the value of this default also changes, the Diff would drop the default before altering the column, then try to re-create this default twice. Fixed.
• In the recent versions the code comparison in comment-insensitive mode didn't properly skip comments: although it ignored text inside the comments, it detected a difference when a comment didn't occur in the same position in both texts. Fixed.

Changes and improvements

• The "owner mapping" configuration is now saved with project options rather than global options. This way you can have different owner maps for different comparisons, which is obviously how it should be. Note: the change means that the owner mapping will not be automatically saved when you leave the Diff, but only when you save the comparison into a .ASQ file.
• Normally the Diff only sees a change in constraint names as a difference when at least one constraint in the pair has a non-default name. When names on both sides are generated by the server, they are ignored both in comparison and in scripting. However, there is an option which controls whether or not to script automatic constraint names ("[x] Hide automatic names" at Options/Scripting/Tables). Before this version, the option only controlled scripting, not comparison. It seemed logical to make this option to affect comparison as well. So now if you un-check "Hide automatic names", the Diff will also synchronize them.
• When you used the "Produce SQL/DROP" command on a selected object, the Diff didn't script the removal of any dependent objects, therefore in many situations the DROPs could not be executed without manually finding and scripting DROPs for the dependent objects as well. This version does remove dependencies before dropping the selected object(s). You can still produce DROP statements just for selected objects, hold the SHIFT key while clicking the scripting commands.
• The "Produce SQL/DROP" and "...CREATE" commands cleared all script from the SQL code window before placing there the generated SQL. Although not exactly a bug, this was inconsistent with the way difference scripts were generated (by adding to existing SQL in the editor window). In this version, all kinds of SQL (DROP/CREATE or difference) are always added to the end of the existing script. If you need to clear the editor window, do it manually before scripting.
• It turned out that in some situations the server scan might receive several (identical) responses from the same machine. As the result, the server scan dialog would show duplicates of the same machine node (with any SQL servers working on that machine also duplicated). Now the server scan filters out any duplicate responses, which guarantees that servers in the tree will be unique.
• The list of SQL Server errors to ignore during script execution (in MSSQLIgnoredErrors.cfg) has been updated, several new "harmless" error codes added. [This file lists server errors which are actually warnings and don't prevent the server from successfully finishing the statement. Those are situations like creating of a stored procedure referring to undefined objects (but the SP is still created) or creating a table with the total VARCHAR size potentially exceeding the row size limit (but that is still a valid table), etc].

Bug fixes

• Scripting. FKs referring to a table being re-created were dropped, but not re-created. Fixed.
• The "Ignore owners" option didn't work corectly. Fixed.
• Due to an error in Beta59, dependent foreign keys were not dropped and recreated when re-creating a primary key or a UNIQUE constraint. Fixed.
• When a column is dropped, the Diff also attempts to drop any extended properties for that column, which is incorrect as the server automatically drops xprops together with their parent object. Fixed.
• Reporting. After generating both schema- and data- reports in the same session, a second (duplicate) set of export formats appeared in the report viewer's Export menu. Fixed.
• The schema reports displayed empty database names in the caption. Fixed.
• Commands to the "Export to..." menu on the report preview form sometimes would just stop working (you click "export to..." and nothing happens, no errors, nothing!). Fixed.
• The "Status" and "Compare type" column in the "Dataset column layout" section of the DataDiff report were not filled correctly (remained empty). Fixed.
• The connection panel. Yet another round of corrections in the server selection combobox should finally get rid of the problem with duplicated server names. Server aliases now keep their names when moved into the MRU part of the server list.
• Fixed the weird mousewheel scrolling in the server combobox.
• On the connection page, the Diff didn't enable the comparison button when the only difference between the two connections is in IP port numbers (i.e. two databases with the same name on different instances of SQL server running on the same machine).

Bug fixes

• When comparing code objects (procedures, etc) with "[x] Lexical comparison" enabled but '[ ] Ignore comments' disabled, the Diff didn't process comments properly. As the result, any single quote (') in the comment was interpreted as beginning a string literal, which, if unbalanced, could easily span to the end of the procedure. Since the Diff always compares string literals exactly, this would lead to unexpected changes reported in procedures differing only in white-space formatting, which would be otherwise found identical. Fixed: the comparison algorithm now recognizes comments and does a kind of semi-lexical comparison within them, ignoring white space but comparing anything else as plain text.
• If one of the triggers is disabled, the difference must show up as "ALTER TABLE DISABLE TRIGGER" statement in the side-by-side view after the CREATE TRIGGER. Done.
• In Options/Data Comparisons/Scripting, when you click in the "Batch every N rows" input field, select the entire input text and then type a number, you'd get " '' is not a valid integer value." message box. Fixed.
• When the SQL Server is configured to return error messages in non-Latin character sets, the message texts were displayed in the Diff as question marks. Fixed.

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.

Changes

• To avoid timeout errors when a complex database is scanned, it may be necessary to increase the timeout value for queries (not to be confused with the connection timeout). Such setting did exist in the Diff, but it was hidden on an unrelated option page ('Executing scripts/General'). This is obviously a connection-specific setting, so it is now added to the connection panel and removed from the options dialog.
• When a saved comparison is loaded, the Diff now recognizes when the two DBs are happen to be on the same server and automatically sets "(o) On the same server" mode.
• DataDiff column configuration: the pull-down part of the filter combobox, containing the recently used expressions, is now saved not in the registry (common for all DataDiff comparisons), but with the settings for this particular table comparison.
• Fixed-size string columns (e.g. 'NCHAR(30)') were loaded with all their trailing spaces, which prevented them from comparing correctly. That is, DataDiff would see any string value in a CHAR(5) column as different from the identical string value in a CHAR(8) column. Now the DataDiff can optionally RTRIM() string columns before fetching them from the server. This option is automatically turned on for CHAR and NCHAR columns, but not for VARCHAR/NVARCHAR (because trailing spaces in VARCHARs are probably needed). However, you can override this setting for any string column in the column configuration dialog.
• One recent addition to DataDiff column configuration, the 'Insert mode' option, was specified as one setting for both compared columns. However, it turned out to be more consistent to use separate setting for each direction (that is, depending on whether you update left-hand or right-hand table, the column value may need to be scripted differently). The column configuration dialog has been changed accordingly.

Bug fixes

• Constraint generation is broken in Beta44: When constraints are restored, the Diff now misses the whole 'alter table... add' part! Fixed.
• The SQL editor window used to keep the CPU 100% busy just doing some idle-loop checks repeatedly. Not so much a bug as an annoyance, but now it is fixed, anyway. Now the windows in Diff are very quiet CPU-wise, unless you are actually doing something with them.
• When DataDiff scripted a DECIMAL column, it used system locale settings. If the locale used comma as a decimal separator, the scripted numbers would also have commas inside, which is not valid in SQL. This version always produces valid decimal literals.
• Decimals with different number of decimal places didn't compare correctly (e.g. the DataDiff would see 1.0000 and 1.00 as different values). This happened because DataDiff tends to use fast binary comparison whereever possible, especially for numeric columns. However, for equivalent DECIMALs the binary representation depends on the number of decimal places, so binary comparison can not always be used. Fixed.
• Hopefully nobody uses that, but SQL Server does allow to include square brackets or quote chanracters into "quoted" object names, like "t[1]". For this to work, the closing brackets must be escaped by doubling them, e.g. 'CREATE TABLE [t[1]]] (...)'. The Diff didn't do the escaping correctly, which would cause scan errors if such objects existed in the databases. Fixed.
• When a session starts with connection having Windows authentication, the login/password fields were not disabled until you switch to SQL login and back to Windows authentication. Fixed.
• After the fix in Beta40 which disables permission scripting for users missing in the target DB, the Diff also stopped scripting permissions in some other situations when they should have been scripted. Fixed.
• The "Hide identical rows" filter in DataDiff didn't work when some of the columns had different data type (e.g. 'int' against 'float'), even if their values in a particular row are equivalent or not. Fixed.
• 'Timestamp' columns in DataDiff must have 'Exclude from inserts' setting as the only one possible for them. Done.
• DataDiff display filters ('Hide unchanged', etc) must not affect the view when a single table is being viewed in DataDiff. As it was, the filters remaining after the last comparison could hide the viewed table completely, whereas the 'set filters' button on the toolbar is disabled. Fixed.
• When viewing a single table in DataDiff, the column configuration dialog incorrectly forced all columns to be 'ignored for inserts', so no INSERTS were scripted. Fixed.
• Due to an error reading comparison files, DataDiff configurations for specific tables were not re-loaded with the comparison. Fixed.
• Several corrections have been made in the code allowing to specify separate row filter expressions in DataDiff.
• In some situations DataDiff's column configuration dialog assigned to columns default scripting modes which aren't even enabled for them. The logic of assigning default descripting modes and other column-wise DataDiff option has been reviewed and corrected in many places.

Known issues

• 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 drops the PK, drops the column, recreates the column with constraint, then tries to add the same constraint again, which is an error.
• 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').

Bug fixes

• DataDiff broken: Recent changes in Beta42 changed format of the connection string being passed to DataDiff when a data comparison starts. As the result, DataDiff was unable to connect to the databases. Fixed.
• AV reading dependencies: Sometimes during the 'Reading dependencies' phase of the scan the Diff would show 'Access violation' warning in the message panel and skip the rest of the schema loading (database properties, extended properties, permissions). Fixed.
• Some DEFAULT constaints belonging to system tables ended up in the schema tree as global defaults (that is, those created by 'CREATE DEFAULT...'), which is an error. Fixed.
• There are several corrections scripting enabled/disabled constraints: (a) When the Diff generated a script to add a disabled ('WITH NOCHECK') constraint to the table, it missed the 'WITH NOCHECK' clause. (b) Besides, simply adding a constraint WITH NOCHECK is not sufficient for it to be disabled, ALTER TABLE NOCHECK is required as well. (c) When synchronizing enabled vs. disabled constraints with automatic names, the Diff inserted the wrong name (that is, in the script for right-hand DB it used constraint name from left-hand DB). All these errors have been fixed.

New and changed features

• Server names in the server selection combobox look just like before, but 'behind the scenes' the Diff now keeps a partial connection string for each of them, not just the server name. So once you select a server from the combobox, this can also automatically set netlib, IP, login or timeout, depending on which information is available for this particular pull-down item (from the server scan dialog, or from server aliases in the registry, etc).
• To fill the servers combobox, the Diff now uses the list of 'server aliases', as specified by the MS Client Network Utility, in addition to other sources it used before.
• The server scan dialog has been modified to allow you to choose connections with a specific network library. You can now double-click not only on a 'server' node in the scanned tree, but on its netlib-specific subnodes as well. Supported are 'TCP/IP' and 'named pipe' subnodes.
• The two database schemas are now loaded by two parallel threads, which can result in significantly shorter loading times. The actual effect of this parallel schema loading will depend on where your servers are: loading from two different remote servers may be almost twice as fast now, whereas for the local server the gain won't be that significant.
• Accordingly, the Diff now uses a new progress dialog during the schema scan. The dialog has two separate progress indicators for each of the databases being compared.
• There have been serious internal changes in the Diff to allow the multithreading described above. One other side effect of these changes is that the Diff now handles aborted or failed comparison in a more reliable way: if the new comparison fails, the current comparison remains intact. The Diff will no longer show the one-sided schema view if one of the connections fails.
• When you change any options in the "Schema Options" dialog, and these changes affect how the schema tree should look, the Diff immediately repaints or rebuild the schema tree, as necessary. If complete schema rescan is required, the Diff first asks if you want to reload the schema now. In previous versions, changes in the settings would not be reflected in the schema view until you manually refresh it.
• The option controlling case-sensitivity of name comparison has been moved from "Schema Scan/Ignored Details" to "Comparison/Textual and name comparison".

Bug fixes

• When you run SQL script in the editor window, and there is a transaction, and the server encounter a SQL error inside the script, the editor would fail to reset its internal "in transaction" flag. When this happens, the editor window can never be closed, because it asks to end the transaction which no longer exists on the server. This has been fixed: the editor now correctly resets its internal flags when a transaction is rolled back by an error.
• The "Difference hints/Long items hints" option on the "Display/Schema Tree" option page didn't work. It is fixed now.
• On the options page "Schema Scan/Ownership", the "Retrieve all/Owned by" and the "Ignore owner names" options were not correctly loaded on startup, so every time you run the Diff, they would be reset to the defaults. Fixed.

Bug fixes

• The SQL generator would sometimes forget to put the table name in ALTER TABLE ... ADD CONSTRAINT [foreign key]... Fixed.
• When a NOT NULL column is added to a table which already has data, the Diff scripts this by first adding the column as a nullable one, then updating it to some default value (like 0), then ALTERing to NOT NULL. However, it tried to use the same sequence for a TIMESTAMP column, which is initialized internally and should be simply added. Fixed.
• Schema reporting command caused access violation if called when you have only one database loaded (not a comparison). Fixed.
• When a report is about to be generated for a single database, the "[x] Report only changed items" checkbox doesn't make any sense. So in this version, it is disabled when reporting a single DB.

Minor changes

• Now the SQL editor blocks the switching of target DBs while in transaction.

Known issues

• There is an AV lurking somewhere in the DataDiff report dialog. I saw it once but so far has been unable to reproduce.
• Both schems and data reporting for a single database actually need separate report templates. Using comparison statistics and color highlighting in this case doesn't seem to make such sense.

New and changed features

• The Diff now correctly processes "WITH EXEC[UTE] AS ..." option for SQL2005 procedures, triggers and functions, as well as "RETURNS NULL ON NULL INPUT" / "CALLED ON NULL INPUT" option for functions. Since the new "EXTERNAL NAME ..." clause in procedures and functions is compared as part of the body text, it does not require any special handling in the Diff. This means the Diff now fully supports procedures, functions and triggers in SQL2005.
• If you try to close an SQL Edit window while there is an open transaction, the Diff will show you a warning message box and won't close the window unless you either commit or rollback the transaction.
• A new "Script users" option is added on the "Scripting/Schema level" option page. This way you can tell the Diff not to script users (groups, roles) when synchronizing the entire databases (from the root of the schema tree). It appears that more often than not you will have different sets of users in the source and the target databases, so this option is initially turned off (that is, not to script users). Regardless of the setting, you can still script users if you select some of them (or the entire category) in the schema tree.

Bug fixes

• The trigger comparison logic has been disrupted in Beta 39, so the Diff detects changes in triggers but can't synchronize them. This is fixed now.
• After an object is synchronized by re-creating it, none of its permissions were re-created. This applied to procedures, functions, triggers and views. Now the Diff re-creates permissions, after re-creating the object itself, as they were in the target database. If you really need to synchronize the permissions, you will have to apply the re-creation script, refresh the schema and synchronize again. This is obviously not a perfect solution, but at least you won't have permissions on the target server suddenly dropped.
• Permissions for missing users: When scripting differences in permissions and some of the users (to whom those permissions are granted) are defined only in one database, the Diff did not take this into account and generated GRANT/DENY/REVOKE statements for those missing users as well. This version corrects this: permissions are synchronized only for users which exist in both databases.
• NFR for triggers: Trigger comparison now takes into account the 'Ignore Not For Replication' option. Since 'NOT FOR REPLICATION' is specified as a part of trigger body, it took some changes in Diff's built-in SQL parser to make it ignore the NFR clause during the text comparison. NOTE: if you wish the NFR ignored for triggers, you must also enable "Lexical comparison" on the "Comparison/SQL Textual..." option page. The Diff now prompts you to do so.
• The SQL editor did not recognize mousewheel scrolling. The mousewheel works now.
• Detecting BEGIN TRANS: The SQL editor is supposed to detect when a transaction begins or ends from the SQL script (e.g. after executing BEGIN TRANS, as opposite to just clicking the "Begin trans" button), but that didn't work in previous versions. Fixed.
• Error in column order: In some rare situations, after repeatedly adding and removing columns from a table (e.g. using MS Enterprise Manager), the Diff would read the columns in a wrong order. Fixed.
• Some options not saved: The flags controlling textual comparison for procedures/triggers/views/functions, such as case-sensitivity or lexical comparison, were not properly saved on exit and therefore would revert to default settings on the next run. Fixed.

Known issues

• When you choose a different server in the connection panel and there is a database name already selected (for the previous server), the Diff does not attempt to verify that the database name is valid for the new server as well. Though not a bug, this creates an opportunity to make mistakes configuring your server connections, so something is going to be done about this.
• When you run a comparison with the left-hand DB incorrectly specified, the Diff reports an error connecting to the left-side DB, but then proceeds reading the right-hand one. When it is done, the tree view remains empty. The correct behaviour would be to stop the comparison if either of the databases is not available and revert to the "no comparison" display.
• When you are synchronizing a user type definition, and this user-defined type is used by some columns in the target database, the columns are not converted to the new type definition, but instead reverted to their original physical type. This is not exactly a bug, but the result may be different from what you'd expect. Probably an option should be added to detemine how you'd like to script such situations. Example: suppose you have a type 'MyNumber' in both databases, defined as 'DECIMAL(10,2)' in the left-hand DB and as 'DECIMAL(14,5)' in the right-hand one. After synchronizing this type left-to-right, any columns of type 'MyNumber' will become of type 'DECIMAL(14,5)' (that is, their actual type remains unchanged, but that is no longer a UDT), whereas the desired result is probably that they remain 'MyNumber', but with the physical type changed according to the new definition 'DECIMAL(10,2)'.

Bug fixes

• Computed columns in tbale function: When you use a table function, and the returned table of this function contains computed columns, SQL Server keeps the column expressions in a way that confuses Diff's schema reader (it says "CREATE statement expected" and occasionaly has an AV). This bug existed in all versions of Diff and is now fixed in 1.90 Beta 39.
• SET ANSI_NULLS and SET QUOTED_IDENTs setting for table functions were not correctly loaded (they were always OFF in the Diff). This error was a result of SQL2005-compatibility changes in 1.90. Previous versions used an undocumented 'status' field in sysobjects to fetch the settings. This worked fine in SQL2000, but didn't work at all in SQL2005. Earlier builds of 1.90 switched to the 'official' way of getting these settings via OBJECTPROPERTY, which worked fine with SQL2005, but it turned out that in SQL2000 it works for all objects except table functions. So now the Diff uses a combined method which always fetches the correct values for the ANSI_NULLS and QUOTED_IDENT flags.
• Triggers didn't compare correctly (gave false positives) between SQL2000 and SQL2005 databases. The reason was that on SQL2005 the Diff couldn't get some of the flags for triggers the way it gets them on SQL2000. In this version, the method of loading triggers has been changed to work correctly with both SQL2000 and SQL2005.
• The Diff didn't recognize enabled and disabled triggers. It does not, including their correct comparison and scripting.
• A new option '[x] Ignore Enabled/Disabled added to the "Ignored details" page of the schema option dialog. It affects the way triggers and constraints are compared.
• Incorrect table re-creation: When the Diff tries to modify a table by re-creating it (see the 'Scripting/Tables' option page), it has to drop all named constraints, so their names can be used again in the new table. However, the previous version first renamed the table, then tried to drop constraints using the old table name (already not existing) in ALTER TABLE. This has now been fixed by changing the order of scripting: constraints are dropped first, then the table renamed.
• The following change addresses what looks like a quirk in MS SQL Server. The server sometimes (?) refuses to ALTER a table function because it considers the function dependent on itself (?). In such situations, a DROP/CREATE sequence must be used instead of ALTER FUNCTION. The Diff can't recognize this situation exactly, so it now always re-creates table functions. Or should this be a separate option?

Known issues

• Trigger comparison doesn't take into account the 'Ignore Not For Replication' option. As the result, NFR and non-NFR triggers (otherwise identical) are always considered different and scripted.

Changes and new features

• The server scan from the connection panel is restored. It is now implemented as a separate popup dialog where you can see server names being added to the list in real time, as soon as servers on your LAN are responding. You can specify the waiting time or cancel the scan whenever you like.
• The server scan dialog also allows you to add any of the found servers into the preferred servers list, one which is initially taken from the list of recent servers kept in registry by MSSQL utilities.
• The connection panel is now extended to include optional network library selection and port selection (for TCP/IP netlib).
• Help file has been updated and now reflects all recent changes to the program, including the new DataDiff features and changes to the Automation API.

Bug fixes

• Fixed an error loading comparison files. If a comparison document specified connections to 2 separate servers, the Diff could not recognize that and incorrectly interpreted the DBs as being on the same server.
• The serial number selection dialog was broken (with assertion failure). This has been fixed now.
• When a schema scan began, Diff's main window didn't have a chance to repaint itself properly until the connection to server(s) is established. As a result, the Diff would appear frozen or broken for a few seconds. This is fixed now.
• There is another change in the connection panel: connection timeout set to 0 (as it is by default) should mean "use the default timeout", but in fact it was interpreted as "wait forever", which caused the Diff to freeze if you try to connect to a wrong server. Now unless you specify a positive timeout, the default timeout (usually 15 sec) is used

Known issues and things to do

• The new features in the connection dialog are not logically complete yet. The server scan dialog can provide netlib and port number as well, but this information currently is not currently passed to the connection panel.
• The following is not actually a bug, but just another opportunity to make a mistake, a thing that the Diff should check, ideally, but so far doesn't. With the introduction of NetLib selection and port number into the connection panel, you must be aware that the usual form to specify named SQL Server instances ("MachineName\Instance" or ".\Instance" for local servers) won't work with some NetLib selections, specifically with the TCP/IP. Instead, you must specify an IP address or a machine name resolvable to IP, whereas the port number determines the server instance.
• Incorrect comparison of permissions (false positives) has been reported, but it has not been reproduced yet.

Changes and new features

• Automation API has been extended to allow loading customized option sets from an .INI file and running comparisons from previously saved comparison documents. You can also specify separate filter expressions when running DataDiff comparison programmatically.
• Removed [Refresh Servers] button from the connection panel. This is logical, as the Diff no longer scans servers. The button will be back as [Scan available servers], once such funcitonality is implemented.

Bug fixes

• Scripting INSERTs in DataDiff was broken by the previous Beta. This important functionality is restored now.
• Some minor corrections in the Automation interfaces.

Bug fixes

• All new features in DataDiff are finalized and tested. Now you can compare a column only present in one DB against a constant, specify case-insensitive comparison or numeric tolerance, set scripting modes, etc.
• Various minor corrections.

New features and changes

• The Diff no longer uses SQLDMO to obtain the list of available servers, using instead the list of known servers the SQL client keeps in Windows registry. This way the Diff doesn't have to spend time pinging the LAN and there is no need to have SQLDMO around. This also happens to solve the "freezing" bug described below

• Besides keeping connections in saved "comparison documents", the last valid connection settings are remembered as defaults for the next session, as it was before 1.90.
• For views and table functions with SCHEMABINDING dependencies are resolved correctly. Normally it is possible to change a table without dropping and re-creating views and functions that refer to this table. However, views and funcs which have the WITH SCHEMABINDING clause must be dropped prior changing a table they refer to. So the Diff now does this.

Bug fixes

• With SQL2005 client installed, the Diff would permanently freeze reading a database (whether SQL2005 or SQL2000 one) if any time before that you happen to pull down the server selection combobox on the connection panel. Apparently SQLDMO somehow messed up messaging or threading within the Diff, so that any subsequent SendMessage call would lock it up. The issue has been solved by removing SQLDMO calls as described above.
• A view WITH SCHEMABINDING must be dropped and re-created to allow a table to which it refers to be modified. Previous Diff versions ignored this dependency, this version resolves it correctly.
• No TRANS in side-by-side view: when the transaction scripting is enabled, the BEGIN TRANS... COMMIT code appeared both in the SQL editor (where it is needed) and in the side-by-side view (where it is not). Now the code is not scripted in the side-by-side view.

Known issues

• All issues from Build33 are still there.
• The dropped server scan feature is to be restored, eventually, but in a more civilized form: activated from a separate [Locate servers] button, displaying the servers as they are found and allowing to cancel the search any time during the scan
• The following is related to the schemabinding issue described above and it is not clear whether is a feature or a bug. Suppose you have table T in each database and a schemabound view V in the left-hand one. Suppose there is a change in the table T to be synchronized. We select the table and synchronize from right to left. The update script will drop view V, make changes to the table, but will not re-create the view. From one point of view this is the expected behaviour, because there is no view V in the right-hand DB and by dropping it from the left-hand DB we move towards full synchronization. On the other hand, the view is a separate object and you didn't tell the Diff to synchronize (drop) it, only to sync the table. Your opinion on this would be appreciated.

New features and changes

• The Diff now works with "comparison projects", which can be saved and opened later. The program also keeps a list of recent comparisons, so that you can quickly re-compare any of them.

• Implemented some basic SQL2005 compatibility. A number of changes has been made in the Diff to ensure that it can read a database schema from MS SQL Server 2005 or MS SQLExpress. The Diff does not yet support most of the new SQL2005 features such as XML schemas, .NET integration or the new variants of SQL CREATE syntax. However, if you have a database freshly ported from SQL2000 to SQL2005, you should be able to use the Diff between them just as before. The only new features this version of Diff does support are these:

  • New XML table columns, both typed and un-typed, are recognized, correctly scripted and synchronized. Typed XML columns are scripted with correct XML schema names, but the XML schemas themselves are not kept as separate entities.
  • The new syntax for MAX length columns is recognized for all character types (CHAR|NCHAR|VARCHAR|NVARCHAR(MAX))
  • According to the new rules in SQL2005, the Diff now uses schema names instead of owner names. However, the "schemas" themselves are not kept as separate entities in the schema tree nor are they scripted (that is, no CREATE/ALTER/DROP SCHEMA yet).
• The reporting and exporting functionality has been added. The Diff now includes a 3rd-party reporting and exporting component, which allows comparison reports to be previewed, printed or saved in any of the following formats: HTML, PDF, RTF, Excel. Comparison reports (although rather basic ones) are implemented for both schema- and data- comparisons. Cloning and limited customization of the provided report templates is technically possible, but is not documented yet.
• The option storage is re-written to allow keeping all program options either in the Windows registry (as it was before) or in an .INI file. The latter allows the Diff to run in "portable" mode: from a removable media like a flash drive, without installation.
• You can now start the Diff with some command-line parameters, including a comparison project's name and switches controlling where to take the program options from (registry or a specific INI file) and where to save them back.
• New DataDiff options caused re-design of the column configuration dialog. Now it has two separate tabs for table-level configuration and for the column-level one. The new DataDiff options include:
  • Option to specify two separate row filter expressions for the two tables;
  • Option to use direct server-to-server synchronization, when the data is passed between the servers or between databases on the same server, as opposite to using the data loaded into the Diff. Therefore, you can synchronize large BLOB data without actually keeping it all in memory (in which case the Diff only needs to keep a 16-byte MD5 digest)
  • Option to throw away identical records and reuse the memory. In this mode only changed rows have to be kept in memory, which means the Diff can now compare tables of practically unlimited size.
  • There are now several comparison styles and various scripting modes that can be changed for individual columns. Comparison styles include case-sensitivity option for character columns and numeric tolerance option for the numeric ones. Scripting modes include "client" (script using loaded values), "server" (script inserts/updates using values directly from the opposite database), "skip" (exclude from the script) and "literal" (to be used for INSERTs when one of the columns is missing).
• Details of data comparisons are saved with the comparison project. This means that once you compare a pair of tables, all table-level and column-level settings are preserved: filter expression(s), ignored/compared/key columns, scripting specifics for each of the columns, etc.
• The DataDiff now uses separate threads to simultaneously load data from both tables. For databases situated on different SQL servers such asynchronous operation may result in significantly faster data loading.
• A new option page "Connection defaults" has been added. There you can specify the server names, logins and connection timeouts you are likely to use for a new comparison. There is also a new option causing the Diff to force certain QUOTED_IDENTIFIERS and ANSI_NULL settings when a connection is being open.

Bug fixes

• A very serious problem was introduced in the last Beta: in trial mode, even immediately after its installation, the program would display the "Trial has expired" message and demand a key to run. This update not only fixes this problem, but actually resets your trial period.
• There is a command in the popup menu of the built-in SQL editor which is supposed to launch MS Query Analyzer from the Diff, passing to it the target connection parameters and the selected SQL text to be edited or executed. As it turned out, the menu item was disconnected from the actual code launching MSQA, so the command was doing nothing. The functionality of the command is now restored.
• Numerous minor corrections throughout the program

Known issues

• [Corrected in Beta 36] The logic for enabling/disabling column comparison and scripting controls in the new DataDiff column selection dialog is not finalized. It is not always clear what the new scripting modes (client/server/literal/skip) will do in each particular situation.
• Reporting is not complete. The default schema report is supposed to include some comments detalizing the differences (e.g. for a table it could be like "columns added", "constraints removed", etc). This information is not yet available.
• None of the new Diff features has been documented in the help file yet.
• Automation API has not been changed yet. It is supposed to support the new "comparison documents" mechanism. At least it should be possible to open programmatically a previously saved comparison document; ideally the API should give access to individual project-level and general options.

New features and changes

• New "Recreate tables" checkbox on the 'Options/Scripting/Tables' page tells the Diff to re-create tables (instead of altering individual columns) whenever it is necessary to keep the correct column order. If you set this option, the Diff will also consider changes in the column order as signficant, display them in the tree and synchronize them. Regardless of this option, the Diff will re-create a table if there is no other way to script a particular change (e.g. making a column IDENTITY in a table with data)
• An option to recreate dependent views has been added (also on the Scripting/Tables page): when columns in a table change, it may be necessary to re-submit all dependent views to the server, so that it could update its internal column lists.
• Yet another new option allows to script the "CREATE if table doesn't exist" logic before any table is created. Currently this feature is only implemented for tables, scripting similar logic for other objects and for DROPs may be added later.
• A new type conversion matrix is now used to properly generate column type changes: in some situations ALTER COLUMN can be used, in others a column must be re-created and the data copied (such as TEXT to NTEXT conversions).
• When adding a NOT NULL column into a table with data, a special SQL sequence is now used to ensure that there are correct default values for the new columns. The Diff maintains a list of default values for each data type, which you can customize on the 'Scripting/Tables/Default values' page.
• The Diff is now much better at resolving dependencies(FKs and other constraints), although some situations can still confuse it.
• The Diff now closes connections after comparison. Previous versions used to keep the database connections open during the whole session. If for any reason a connection would expire or got broken while viewing the differences, you'd be unable to run any scripts or refresh the comparison. As of ver. 1.80, both connections are closed as soon as the schema data is loaded, then re-opened as required when you start executing the scripts.
• The registration dialog is redesigned for easier activation and re-issuing of the keys.
• The "Choose serial" dialog is also redesigned and some new methods of license binding are added: low-level (format-resistant) IDE disk binding and binding to a portable USB storage device (flash drive). NOTE that the flash drive binding may or may not work with your particular flash drive device, depending on its model and manufacturer.

Bug fixes

• Automation: The SelectNextDifference corrected. This method didn't work correctly when several schema objects had been previously selected.
• DataDiff: in some unusual situations the program would try to synchronize rows with changes in "hidden" columns. Although (correctly) no code would be generated for such rows, all the extra checks and issuing of warnings for each row would slow the scripting down tremendously. The new version doesn't have this problem so that DataDiff scripting is very fast.
• In one of the recent updates, the Diff lost its ability to sort schema items alphabetically. The correct ordering has been restored.
• In some situation, the same 'DROP INDEX' statement could be scripted twice. This has been fixed.
• A few other minor corrections in various places.

Corrections in DataDiff automation interface

• SelectNextDifference() method did not work correctly when called with Expand parameter (SelectNextDifference(True)): it selected the next changed row starting from the first selected row, so it never went further down than the second changed record. Fixed.
• GetRowIndex() function returned wrong value. Fixed.

Changes in DataDiff GUI

• You can now use Shift with "Select Next/Prior Changed Row" commands to select multiple rows. This trick works both with toolbar buttons and with keyboard shortcuts. That is, you can use Ctrl-Alt-Shift-DOWN|UP to select the next or previous change, adding it to all previously selected rows.
• Tooltip texts for the "Show Difference script" commands incorrectly indicated Ctrl-Shift-LEFT|RIGHT as shortcuts for these command. Actually, the shortcuts are Ctrl-Alt-LEFT|RIGHT. The tooltips are now updated accordingly.
• DataDiff now opens SQL editor as an "owned" window, which means that the editor window stays on top of DataDiff, but is not modal (you can freely move focus between DataDiff and code). Together with the Ctrl-Alt-<Arrows> shortcuts described above, this means you can conveniently traverse the dataset from one change to another, instantly synchronizing records in either direction.

New features

• "SaveAs" in Unicode or UTF8. The "Save As" dialog in the SQL editor now allows to specify saving in OEM (as before), Unicode or UTF8 formats. NOTE that it does not make the program truly Unicode-enabled: it can only display and correctly save scripts containing national character from a single code page. For example, you can have Latin and Cyrillic letters, or Latin and Greek, but not Cyrillic and Greek together.
• Changed some defaults in the Schema Options dialog. Now a freshly installed copy of Diff will compare object- and statement permissions and will check whether tables have data or not. Before, these two options had to be manually enabled from the Schema Options dialog. These changes in initial setting don't affect those who is upgrading their existing copy of AdeptSQL Diff.

Bug fixes

• xplog70.dll is no longer needed. That DLL is a part of standard MS SQL Server distribution, but sometimes DBAs remove it for security reasons. With xplog70.dll removed, the Diff could no longer access the schema, although it was only needed to obtain server's version number. Now the version information is fetched in a different way, so schema loading does not depend on that DLL.

New features

• "Hide LEFT-only objects" filter added to the schema tree filter menu. For historical reasons, previous versions only had "Hide RIGHT-only objects" option there.
• "Hide changed rows" filtering option is added to the DataDiff view. It didn't look necessary until a user pointed out that sometimes he'd only be interested in "left-only" and "right-only" records as it allows him to quickly locate incorrectly entered primary keys.
• DataDiff filters can be persistent now. This is implemented in exactly the same way as for the schema tree filters: there is now a new item in the DataDiff filter menu, indicating whether the filters should be stored in the registry.

Fixed bugs

• SQL_VARIANT was not supported. Columns of variant type were not correctly loaded and were displayed as NULL. This has been fixed.

New features

• Redesigned "Ignored Details" page: controls in this option page has been rearranged in a (hopefully) more logical order.
• Added "Ignore collation" checkbox on the same option page. It allows to ignore the differences in the COLLATE attributes of table columns and the entire database. Both the comparison and the scripting code have been modified to take into account the new setting.
• New "Summary collections": Previous versions would display copies of all foreign keys and indexes (normally scattered throughout the schema tree) under a special node at the bottom of the tree, called "Summary collections". This version adds a new option page ("Display/Summary collections"), where you can choose which exactly objects should end up in their special "summary collections". Besides FKs and indexes supported before, you can now display there other constraints, triggers, permissions and extended properties.
• For top-level objects that have different owners in the two databases, but considered matched according to the specified "owner map" (see the "Schema Scan/Ownership" option page), the program now displays both names in the schema tree (i.e. "dbo.table1 / user1.table1" instead of just "dbo.table1")
• The help file has been updated according to the GUI changes described above

Fixed bugs

• Column collations not synchronized correctly: when the only difference in a given character column was its collation, previous versions of the Diff would display the columns as changed, but would be unable to produce the synchro script for them. This is fixed now.
• Problem comparing encrypted procs: as described here, previous versions would only respect the "consider changed" option when the unencrypted DB is on the left-hand side of the comparison. In this version, the comparison works correctly in both directions.

New features

• One is "Script with SQLShield encryption", which is only useful for those who have SQL Shield server plugin installed. When this option is enabled, the Diff would generate a special "magic" comment ("WITH /*sqlshield*/ ENCRYPTION") causing the procedure to be encrypted in a special "unbreakable" way.
• The other option is "Protect from tracing" and it causes another "magic" comment to be generated for encrypted procedures ("WITH ENCRYPTION /*sp_password*/"). When a procedure "WITH ENCRYPTION" is sent to the server, its full definition can be caught in a MS SQL Profiler log, which creates a security risk. However, if the profiler finds "sp_password" anywhere in the SQL (even in a comment), it would not show the statement text in the log.

Fixed bugs

• Cleaner startup and closing of the current comparison: previous versions used to start-up with the empty schema panel visible, and leave visible the side-by-side panel and the message panel after you close a comparison. Now the program shows nothing but the connection dialog when there is no connection open.
• There is an "Include owner name" group of options on the "Options/Schema scan/Ownership" page of the Schema Option dialog. One of its settings - "Don't include owner name" - was not correctly saved, so that after you choose it and restart the program, it would be back to the "Only if different..." setting. This version fixes the bug.
• In trial mode, the previous versions used to look up a license file on removable disks (CDs or flash-memory). In some configurations it would cause ths system to pop up a "No disk" error message box. This disk enumeration is now removed.

Other changes

• The help file has been updated to reflect the changes and the new options appeared in the recent releases.

Known issues

• There is an option telling the Diff to consider any encrypted procedure different from its open-text counterpart and therefore always generate update script for such entities. However, this only works with the open-text SPs in the left-hand DB and the encrypted ones in the right-hand DB. Compare it the other way around, and the two procs would always be shown as identical. This problem will be fixed in a future release to make the comparison logic work same both ways. Meanwhile work around the problem by specifying the DB with encrypted procedures as the right-hand one in the comparison.