best way to migrate a MySQL DB to MSSQL 2005

Hi Edukate,

Here is a straight forward guide from a reliable source....  non SSIS method...
http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx

SQL Server White Papers: Guides to Migration from MySQL, Oracle, Sybase, or Microsoft Access to Microsoft SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?FamilyID=3f0f2a3f-dff9-49cd-8ea7-581aa7a303a4&displaylang=en

hope it helps...

thanks I tried the codeproject guide before I got into the SSIS method but ran into an error 'Cannot get the column information from OLE DB provider'.
I was trying to use the mysql ODBC driver so is there any way to do this?

OK I've got the mysqldump.sql now too whether this could make things any easier??

I had this issue last June 2008. I was trying to test migrate our MySQL to MS SQL 2005 wasn't successful. When it was geting complicated, I then opened a case to Microsoft showing how I am doing it, until we later found out that we cannot migrate directly to MS SQL 2005. What we did was migrate to MS SQL 2000 then upgrade to SQL 2005.

They might have updated or probably had included the fix on the service packs nowdays.

This is what we did before (see if it might help though):

1. Downloaded MySQL Connector/ODBC 5.1.
2. Installed on a box having SQL 2000 instance.
3. Created a user DSN by using Mysql ODBC driver.
4. Started Import and export wizard of SQL 2000.
5. Used other(ODBC data source) and selected the user DSN created.
6. Went to the next step and selected Microsoft OLE DB provider for SQL server.
7. Under server list selected 2005 instance and created a new database for the import.
8. Ran the import wizard.

That sounds similar approach to the codeproject solution though I haven't tried the import wizard for this.. Not sure if it's capable as I couldn't find the MySQL provider in list for this.. Will test out further

OK i tried just browsing the mysql db from the linked servers > mysql  > catalogs > dbname > tables > script table as.. select to.. and could see all the existing tables in mysql db... (235 of them) however I cannot select * from any of them.
I instead get this error
"[MYSQL].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object"  (note the .. between database and table names)
which looks like an old issue here:
 http://kbalertz.com/971261/table-reports-error-linked-servers-created-using-MSDASQL-provider-Server-Server.aspx

This blog also mentions how to get around it but as far as I can tell I've done exactly that but the error still persists:
http://legalalien.org/graveyard/index.php/2007/11/26/contains-no-columns-that-can-be-selected-or-the-current-user-does-not-have-permissions-on-that-object/

Ok after spending most of yesterday and today working on this the only way I've found to do this is using SSIS with DataReader Source then creating then configuring a new OLE DB destination for each table with mappings.
This will take a very long time to do and I have to repeat it again on production system but I haven't found any other way that works :(

Actually I've just found likely an easy way to do this:
In PHPMyAdmin you can export to different formats such as SQL.
I've opened up the first 1000 or so lines of the Insert Query generated (this is about one tables worth of metadata and data)and parsed it in SQL.
MSSQL returned half a dozen errors which I assume is just differences between how MySQL and MSSQL use SQL Syntax..

Does anybody know of any tools that can convert the syntax or maybe just the differences between them (likely the same errors repeating for each table)..

Below are the errors:

Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near 'adodb_logsql'.
Msg 102, Level 15, State 1, Line 49
Incorrect syntax near 'mdl_assignment'.
Msg 102, Level 15, State 1, Line 308
Incorrect syntax near 'mdl_assignment_submissions'.
Msg 105, Level 15, State 1, Line 866
Unclosed quotation mark after the character string '<table class="MsoNormalTable" style="border-right: medium none; border-top: medium none; margin: auto auto auto 5.4pt; border-left: medium none; border-bottom: medium none; ...

Select allOpen in new window

This is not hte best approach especially when you have hundred thousand rows on your table. This would only be applicable to static data or small tables. What I did on that style is scan all the insert statements and see to it that the synbtax is SQL 2005 compatible.

Here is a documentation to migrating from mySQL to MS SQL 2005
http://www.microsoft.com/Sqlserver/2005/en/us/white-papers.aspx

Look for "Guide to Migrating from MySQL to SQL Server 2005". It talks about detiled convertion issues and how to handle them.

Thanks faiga16
Did you try all the approaches 1-5 and none of them worked?
I have spent much time trying 1, 3, and 4 - all without success

Number 2 - would you suggest dumping from MySQL as .csv or similar format, then importing? If so what would be lost such as relationships etc?

Number 5 - do you mean this wouldn't work if DB has more than 64 tables?

Number 6 - I have access to another server with SQL2000 - do you think it would be a good idea to try replicate the DTS tool solution then export the SQL 2000 DB for import into our SQL 2005 server?

Yes, I tried all of them and ended up with approach #6.

#2 fortunately, we had only few relationship tables and I didn't encounter any issues with that. The only draw back here is you migrate csv file one at a time

#5 At that time, YES! I didn't look into that issue when trying to figure out what's wrong on this approach, it took me a while to research and found out that it only support 64 tables?!? I am hoping I'm doing something wrong here. But you can try it for yourself and see. They might have added fix to this (I did the project migration June 2008)

#6 I ended up using this approach. It has direct mapping of objects from MySQL to SQL 2005, you can select multiple tables to import at a time, and applicable to all data sets (static, log, transaction tables) .
With this approach SQL 2000 is just a middleman between MySQL and SQL 2005.

I don't understand why they don't have a direct migration from SQL 2005. But you can see it for yourself (as I've said I did the migration 2 years ago). Try using SQL 2005 Import/Export Tool, see if you can do a direct import from MySQL. I do not have MySQL test database right now so I cannot replicate this using SQL 2005.

We have a SQL 2000 box that we'll try to clone to a VM to try out this migration soon.. Unless anybody else has other suggestions that has worked for them?

Thanks for the help / suggestions however we ended up sticking with MySQL for the time being.
If we get time we'll try the approach using MSSQL2000 in future

Havent had chance to try this yet