Sql-server – MySQL Workbench copies Schema, but not data, from SQL Server 2008

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307 

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;

Was trying to solve the same problem and stumbled here... It's a year late, but better late than never...

OSX uses iODBC as ODBC layer, so FreeTDS must be compiled against iODBC for MySQL Workbench to connect.

Since I struggled with iODBC as well, what I ended up doing was to create a custom "library installation" to run this.

Steps are as follows:

1. Make sure you have XCode and command line development utilities installed. You will compile lots of stuff!

2. Download and compile iODBC (I used 3.52 from www.iodbc.org). Make sure you install somewhere "isolated" with "--prefix=/usr/local/iodbc" for instance;

3. Download and compile FreeTDS:

   • cd to freetds source directory;

   • type export CFLAGS="-arch i386"

   • type export LDFLAGS="-arch i386";

   • run config with ./configure --prefix=/usr/local/freetds --with-iodbc=<path_to_iodbc_in_step_1> --enable-odbc-wide

   • (MySQL Workbench NEEDS 32-bit archtecture; the exports will ensure that, since Mavericks - my OSX - is 64 bit.)

4. Download and install ODBC Administrator from Apple (https://support.apple.com/kb/DL895?locale=en_IS - version 1.0 worked for me);

5. Run ODBC Admin, go to "Driver" tab and create a "TDS" driver; Point "driver file" to /usr/local/freetds/lib/libtdsodbc.so (it should be there after step 3!)

6. Go to MySQL Workbench Migration Tools and choose as Connection Method "ODBC (FreeTDS)". In "Driver", type the name you used in step 5 ("TDS") and fill the other info as needed.

7. "Test Connection" and go have a cup of your favorite drink before moving on! :)

One entire workday "invested", but it did work.

UPDATE

After my cup of coffee, I moved on with my test migration, only to find out my SQL Server connection was NOT actually connecting (correct user, correct password). Another morning's work and here are the adjustments:

1. Make sure you compile iODBC to 32-bit architecture. To do so:

   • use the same export you will use when compiling FreeTDS (CFLAGS,LDFLAGS);
   • use --prefix=/usr/local/iODBC32. Make sure you compile to different directory or you may break your current ODBC install.

2. Make sure you compile your "32-bit" FreeTDS version pointing to the "right" iODBC with:

   • ./configure --with-iodbc=/usr/local/iODBC32

This should do the trick.

Another piece of advice:

MySQL Migrations will use exec sys.sp_databases to fetch info about the schemas to be migrated. If you're not using sa to migrate (which you probably shouldn't!), make sure your login has enough privileges on the master database (I myself created a "temporary" clone of sa to migrate); otherwise, MySQL Migrations will have trouble fetching and copying.

All these things followed and I finally completed a successful migration (minor manageable collation problems).

Hope this helps,