DTS from SQL -> SQL is changing Varchar field to Text field

[TomLeMes]

We'll often want to do a quick DTS (Export data...) between our dev server and our live server. A problem with this is that although the data and tables are copied across successfully, any varchar fields are converted to text(16) fields. Has anyone come across this before? Is there a way of stopping it? (If we create a backup file, and then restore to the other server, the varchars remain as varchars, but using this backup method is a bit of a pain). Any ideas on why this is happening?

Note that both servers were setup ages ago by someone else and it seems they have different Collation: SQL_Latin1_General_CP1_CI_AS on one and Latin1_General_CI_AS on the other. Perhaps I'm clutching at straws here, but could this be the cause?

 

[MartinCroft]

How are you using the DTS wizard. Are you using the copy Objects and Data, this will recreate the objects with the same table structure on the destination server.

If using use a query to specify either create the empty table scripting of the source environement or if using a deafault table click transform and specify exactly what columns and datatypes

Hope this helps

 

[TomLeMes]

Thanks mysticmart, the instructions I was given were to use the 'Copy table(s) and view(s) from the source database' option. The copy objects works perfectly, thanks a lot,

Tom