Copying tables from one SQl database to another changes varchar to text

Copying tables from one SQl database to another changes varchar to text

Post by kim » Sun, 25 May 2003 21:59:03



When I use DTS to copy tables from one SQL 2000 database to another database
on the same server (or even another sql Server) sql changes all varchar
fields to Text 16. I am running sql server 2000 with the latest SP and using
DTS - Export data or Import data function fromt he tools menu.
Is there anyway to copy the tables so that all fields remain as they are in
the original table?

Thanks
Kim

 
 
 

Copying tables from one SQl database to another changes varchar to text

Post by Jan Brown [MSF » Fri, 30 May 2003 23:06:30


Hi Kim

I have tried this here with a SQL Server 2000, SP3 machine and DTS export
wizard created fields as they were in the source table.  For example, I
used the following script to create my dummy table:

create table testDTS (f1 varchar(10), f2 varchar(16), f3 int)
go
insert into testDTS values ('this is aa', '1234561234123421', 1)

Then from DTS, I chose All Tasks, Export Data.  During the wizard, I
clicked on the ellipse (...) in the Transform column of Select Source
Tables and Views and could see that the data type was varchar.  I completed
the wizard and the data was transferred with the correct field type.  I
tried with longer fields (greater than 255) and the corresponding datatype
remained as varchar.

Is the above a close example to the process you are using?

This posting is provided "AS IS" with no warranties, and confers no rights.

Kind regards
Jan Brown
Microsoft SQL Server Support

 
 
 

1. SQL Server - Copy a table from one database to another database

Hi.
I need to keep a copy of a table T along with its data in a Database D1 to another database D2. New table shoould be indentical to old one by copying all primary key and other properties.

Sriparna.

**********************************************************************

Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

2. Help Connecting Applets to Oracle Backend

3. Copying table from one database to another one

4. Using timestamt, varbinary(8) in SQL quieries

5. Copy a table from one Database (AS400 or SQL server) to another (MDB)

6. AN: I Capture - Capture / Create Windows Animations ( AVIs)

7. Simple select query or ...

8. SQL Server - How to copy table's indexes from one database to another

9. Copying the changed data from one table to another

10. copy text data type from one table to another

11. Copying text fields from one table to another

12. Copy a Text datatype field from one table to another