Hi,

I have a field which data type is wrongly defined. I would like to change it. However, SQL Server unable to convert it and complaint unable to convert type X to type Y even though that is no data on it (but got records) via DDT.



As I know, we can use copy method as workaround - Create new table, APPEND back data to new table, drop old table and rename new table with old table name. But how to do it using DDT?



Thank you

Hi,

Thank you reply.

The steps you shared seem required manual work, such as copy the data from old field to new field. Am I right?

Anyway I can "automate" it?



Thank you

Well, there are certain data types that cannot be converted without manual intervention.  Others can be converted.  For example, converting a VarChar into an Integer is difficult as the data within the VarChar is not going to reliably convert.  In fact, if you do this within SSMS it will not always convert the data over as well.  However, SSMS will do this for you in more scenarios than the DDT or SMO.  So if this is an isolated issue, you may consider using SSMS to modify the table to "fix" the improper definition to retain the data then the DDT will work from that point forward.

The other option is to create a pre deployment script to fix this particular table prior the structures being deployed.