Re: migrate from SQL Server 2005 to MySql 5.1.39

[Sergio Andres De La Cruz Rodriguez]

Hi:

>> Why ?

As the error message you got explains, it is expecting BIGINT data literals and is receiving DECIMAL literals instead.

>> Is it possible make this target definition ?

Yes, but it's not as straightforward. Follow these steps:

1. Update Workbench to the most recent version (5.2.47 as of this writing).

2. Run the Migration Wizard and modify the generated MySQL code in the Manual Editing page, as you did before.

3. Create the target database as usual, but in the Data Transfer Setup page uncheck "Online copy of table data to target RDBMS" and check instead "Create a batch file to copy the data at another time". When you move to the next page, a batch file will be created that will contain instructions on how to call wbcopytables.exe for your tables. This is the application that will transfer the data.

4. Edit the generated batch file and type there in the suggested places your source and target server passwords. In this file you will find a specification for each table column that will be copy. Find the one that corresponds to the modified table and change the id column name with a T-SQL expression that casts the DECIMAL literal into a BIGINT literal. You should be ok with something like "CAST(id as BIGINT)" (without the quotes). Please be careful when editing the table definitions, as tabs and spaces are significant. Save your modified batch file.

5. Run this batch file to transfer your data. Keep in mind that wbcopytables.exe needs the base.dll from Workbench install dir. So either run the batch file inside the WB install dir or copy the dll to the dir where your script lives.

Hope this helps.

Sergio A. de la Cruz Rodríguez
Software Developer
Oracle Corp.