How do I prevent/track truncation errors on table synch

s_solt

If I have a client (target) database with populated tables and I want to synchronise it with my development (source) master, there may be records which include data which exceeds the preferred size.

There are 3 ways of dealing with this, none of which seem supported by SQL compare:

1) Option to omit field length from the comparison or not reduce field lengths on the target.
2) Highlight those fields where truncation will take place - ie where data exists which is longer than the source field.
3) Turn ANSI_WARNINGS OFF for the synchronisation and allow truncation - I can do this by editing the script but it should be a Project option.

At the moment I have had to write a script with cursors and dynamic SQL to identify the numbers of records for each field in each table for which truncation WILL occur. This seems a pity with such an otherwise capable tool.

Stephen Solt

Eddie D

To take each point you have raised in turn:

1)Option to omit field length from the comparison or not reduce field lengths on the target.

The idea of SQL Compare is to synchronize database schemas, so that the schemas become identical. But I will pass on your comments to our developers.

2)Highlight those fields where truncation will take place - ie where data exists which is longer than the source field.

The software does warn if a column will be truncated. In the summary phase (Step 3 or 4), there is a warning tab, the warning is high and informs "the column [column name] on table is being truncated. There may be loss of data."

3)

Turn ANSI_WARNINGS OFF for the synchronisation and allow truncation - I can do this by editing the script but it should be a Project option.

By default the setting for ANSI_WARNINGS is OFF. When dealing with indexes on computed columns and indexed views, the are anumber of ANSI DEFAULTS that must be turned on, one of these settings is ANSI_WARNINGS. The script generation process includues the statement to turn on ANSI_WARNINGS ON for this reason.