Troubleshooting

This topic provides information that may help you to understand your comparison and synchronization results. You may also wish to refer to other topics in Tips and Troubleshooting (see the Contents page of the Help).

Missing tables or columns in Project Configuration

If the structure of the databases that you are comparing has changed while you are working on a comparison project, those changes are not automatically shown in the Tables & Views tab on the Project Configuration dialog box. For example, if you have added columns to both databases, these will not be shown and will therefore be omitted from the comparison.

To update the Tables & Views tab on the Project Configuration dialog box, click Refresh. For more information, see Selecting Tables and Views and Mapping Errors.

Identical CLR or XML data is flagged as different

SQL Data Compare considers the collation for CLR and XML data. Therefore, if the collation is not identical, differences are reported.

Data has not been synchronized

This may occur if:

there are triggers defined on the tables
If you have a trigger defined on a table that inserts data into another table on INSERT, DELETE, or UPDATE, the data in the tables will change as the synchronization is run, which will cause unpredictable results. To avoid this, select the Disable DML triggers project option before you generate the synchronization SQL script.
primary keys are defined on columns with differing collations
If you compare tables that have primary keys defined on columns that have different collations, SQL Data Compare may produce unpredictable results.
columns contain timestamp data
SQL Data Compare cannot synchronize data in timestamp columns.
tables do not have identical structure
If the structure of the tables that you are synchronizing is not identical, SQL Data Compare may produce unpredictable results.

CLR data has not been synchronized

Data for CLR types can be stored as string or binary values. When CLR data is compared, SQL Data Compare always compares the binary representations. However, by default, when CLR data is synchronized, SQL Data Compare synchronizes the string representations, because binary formats are not always compatible.

If the binary representations are not compatible, they are always displayed as different even if the string representations are identical. String representations do not always contain the full information about the data, so when the databases are re-compared using the binary representations following synchronization, they may be displayed as different.

If you know that the binary formats are compatible, you can select the Transport CLR data types as binary project option to force SQL Data Compare to synchronize the binary representations.

Primary keys, indexes, or unique constraints are not dropped for synchronization

If you select the project option Drop primary keys, indexes, and unique constraints, note that primary keys, indexes or unique constraints that are selected as comparison keys are not dropped for the synchronization.

Insufficient disk space

SQL Data Compare may be unable to compare databases if there is insufficient disk space. SQL Data Compare uses temporary files when it compares the databases. You can decrease the size of the temporary files by restricting the rows that are compared, for example, by using a WHERE clause or excluding objects that you know you do not want to update (see Selecting Tables and Views for details).

You can decrease the size of the temporary files by selecting the Compress temporary files project option (see Setting Project Options for details). Note that selecting this option means that you cannot sort the results following the comparison.

You can also decrease the size of the temporary files by clearing the Show identical values in results project option and selecting the Use checksum comparison project option (see Setting Project Options for details).

The location of the temporary files is defined by the RGTEMP environment variable, or the TMP variable if RGTEMP does not exist (see your Windows® documentation for information about environment variables). Note that changing the TMP variable will affect other programs that use the variable.

Cannot sort columns in the Row Differences pane

If you select the project option Compress temporary files, note that you will be unable to sort the comparison results in the Row Differences pane by clicking on a column header.

Rollback on script failure or cancellation

If a script fails, or if it is cancelled, in most circumstances changes are rolled back. SQL Data Compare uses transactions to do this.

If you have cleared the project option Use transactions in SQL scripts to remove transactions from the synchronization SQL script, no changes are rolled back when the script fails or is cancelled. This may be useful if you want to run a script up to the point of failure, for example for debugging.

SQL Data Compare always warns you if it is unable to roll back changes.

Column mappings

SQL Data Compare cannot compare certain combinations of data types. The following illustration shows the data types that can be compared.

Note the following:

You can compare a timestamp column with another timestamp column but you cannot synchronize timestamp columns.
You can compare an xml column with another xml column but you must ensure that your XML schemas are compatible.