Data transfer from MS SQL to MySQL

[Murtaza Millwala]

Hello,

I have been assigned a task to transfer data from MS SQL server table to a MYSQL server table. I am using MYSQL 4.1 and SQL Server 2000.

The table name in SQl and MYSQl including the attribute names have been kept same.
I have tried changing the data types in both as well changing engine types for MYSQL table.

The table name is Store_Details

Attributes for MS SQL

Store_ID int NOT NULL,
Address_Line1 varchar(100),
Address_Line2 varchar(100) NULL,
Suburb varchar(50) NULL,
State varchar(50) NULL,
Postcode varchar(5) NULL,
Country varchar(50) NULL,
Telephone varchar(20) NULL,
Fax varchar(20) NULL,
Email varchar(100) NULL,
Primary key (Store_ID)

Attributes for MYSQL

Store_ID mediumint NOT NULL,
Address_Line1 varchar(100),
Address_Line2 varchar(100) NULL,
Suburb varchar(50) NULL,
State varchar(50) NULL,
Postcode varchar(5) NULL,
Country varchar(50) NULL,
Telephone varchar(20) NULL,
Fax varchar(20) NULL,
Email varchar(100) NULL,
Primary key (Store_ID)

Have tried using text and longtext as datatype for MYSQL Store_Details table

Errors I get using varchar in MYSQL table Store_Details:

"Insert error 6 on DBTYPE_STR 'Address_Line1' data overflow"

Error I get when I use longtext as datatype for MYSQL Store_Details table

"The number of failing rows exceed the maximum specified"

Can someone please help me to solve this problem.

The data needs to be transferred on a regular basis. Have tried using DTS package, but it generated the errors above.

Can someone please advise me on how to solve this problem.

Thank you.

Sincerely,

Murtaza Millwala