This is a known "feature" of DTS. If it transfers objects with default options it won't even transfer primary/foreign keys or autoincrement fields. The "copy object" task gives some options to copy triggers and primary/foreign keys and extended properties but default constraints are not copied. (bug, some fool programmer's crazy idea? we don't know). It is better as someone suggested to simply script out the objects via enterprise manager or other tool and then execute that script to create the destination objects. You can then use DTS to transfer the data.

Brian Freeman
(770) 916-0595 ext. 415
Carnegie Technologies/Bluewave Computing
www.carnegie.com and www.bluewave-computing.com

Chip Smith wrote:
> Hi list,
> SQL2K on SP1 with cluster.
> WIN2K SP3
> Did an import of a table from one server to another , ie; used dts to
> create and copy the table.
> Worked fine, however it did not bring the column default settings
> along with it. Those had to be done by hand.
> Question is , is this normal behavior?
> Caused a lot of problems until we discovered a failed insert in an
> obscure log file.
>
> TIA
>
> -- Chip
> OCP DBA
MS Sql Server LazyDBA home page