Source Type 200 in SQL Server Import and Export Wi

Question

I am trying to import data from one database in a server to a new table in a different server, using SQL Server import and Export Wizard. (SQL Server Management Studio 2012)

In the wizard, I checked "Write a query to specify the data to transfer", and the SQL statement returns data containing the following four columns:

+-----------------------------------------------------------------------------+
| ID(varchar(100))  |  Title(text)  |  Description(text)  |  IsActive(tinyint)|
+-----------------------------------------------------------------------------+

I want to change the types for the new table to

+----------------------------------------------------------------------------------------+
| ID(varchar(4))  |  Title(varchar(200))  |  Description(varchar(2000))  |  IsActive(bit)|
+----------------------------------------------------------------------------------------+

So, in the "Column Mappings" page(In the "Select Source Tables and Views" page, I clicked "Edit Mappings..."), I changed the Destination Type to the above types. Then, after clicking "Next", in the "Review Data Type Mapping" page, I get an error that says "Found 3 unknown column type conversion(s). You are only allowed to save the package"

The Data type mapping shows the following information:

icon    Source Column    Source Type    Destination Column    Destination Type    Convert
----------------------------------------------------------------------------------
error   ID               200            ID                    varchar
error   Title            200            Title                 varchar
error   Description      201            Description           varchar
warning IsActive         tinyint        IsActive              bit    

Even if I don't change the data type in the "Edit Mappings..." page, I get the same error.

I don't understand what "200" means in the context of a data type, and how can I import this data to a new table in a different server?

I appreciate any help.

Answer 1

I bet that text columns aren't able to be inserted into varchar columns with the wizard. Depending on how big the table is, you could export the source to csv via the SSMS and then import it. That should work but if you have multiple tables to import you could add a linked server. Then you can just qualify the old table or the new table like this:

insert into [new_server].database.dbo.tablename
select * from old_table

I know SQL2000 is a pain for creating linked servers though which is what I'm guessing you are trying to export from since you have text columns.

Answer 2

Fastest solution is to export the data to a new table in the same database (source) using the import/export wizard. Then export the data from the new table. Somehow the import/export wizard works his magic (not really) at the time it creates the new table. Thank you Jyoti for ending the pain it was to use the import/export wizard.