How do I prevent/track truncation errors on table synch
s_solt
Posts: 11
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
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
Comments
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.
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)
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.
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com