SQL 2000 DB to MYSQL DB

I have a SQL 2000 database that I'd like to be able to export it & then import it into a MYSQL database...is this possible? Can I do it for free? Or does SQL have a tool built in so I can export it so I will be able to import it to a MYSQL database?
LVL 15
wantabe2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
SQL 2000 can probably do this using DTS or Linked Server to MySQL. Just be aware of the data type differences and level of support for ANSI SQL and differences in syntax/behavior in general based on what all is in your database. For example, there is not a concept of a computed column or uniqueidentifer data type, etc. Remember it is schema.table and not database.schema.table, i.e., database is analogous to "catalog" in ANSI terms and mysql does not use catalog. So just ensure you have a good map of what you will have to alter in design/structure, but the data should be easy to move with one of the methods specified even if just to export to CSV/Excel and then use Import on MySQL side.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wantabe2Author Commented:
I'm not that familair with MYSQL or SQL...So you are saying I should be able to export the SQL DB to a CSV then import it into the MYSQL?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you are not familiar yourself, you likely should give the final task to someone that is experienced :)

but to repeat above comment:
yes, you can export your sql data in a way that it can be read with MySQL import.
you can find the relevant MySQL reference here:
http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

to "export" from SQL Server into such a format, you can use sqlcmd tool (http://technet.microsoft.com/en-us/library/ms170207.aspx), bcp (http://msdn.microsoft.com/en-us/library/ms162802.aspx) or the wizards (http://msdn.microsoft.com/en-us/library/ms140052.aspx)
My experience is that you better start scripting anyhow, as you will eventually run this type of export a couple of times until it works out ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kevin CrossChief Technology OfficerCommented:
Data. But I would look first to direct transfer using DTS or Linked Server. Both use the connectors provided by MySQL.

P.S. If you are not familiar with MySQL, why are you moving that direction if you don't mind my asking? I use both, so I am really just curious. With the free edition of SQL Server, you can transition more seamlessly from where you are today if either system will be a learning curve. The advantage is that if you have to learn SQL 2000, learning SQL 2008 R2 won't be far off. Whereas moving to a new DBMS, you will have to learn SQL 2000 plus the new system whether that is MySQL or otherwise.
0
wantabe2Author Commented:
@mwvisa
It's because I legacy applications we use & there is a real need for the upgrade to new hardware & we don't have the new $$$ for SQL license. It has to run on full blown SQL or MySQL....
0
Kevin CrossChief Technology OfficerCommented:
Okay. The suggestions above should apply. I was just curious as if the features of full blow SQL are not absolutely required as evidenced by being able to move to MySQL, then I would think SQL Express Edition would be just as good. Anyway, given you have no choice. I would use references provided by a3 to familiarize yourself. Note: SSIS is SQL Server's replacement of DTS. So, specifically with Import/Export Wizard, you will find that information informative to you in some ways even though it is for SQL 2005 and higher. And as a3 asserts, it will likely take more than one attempt because of some of the things I spoke of earlier like data type changes.

The advantage you have is if your vendor supports both, then you should already have data structure setup for MySQL, right? That alleviates that piece and you just have to move data from one structure to the other. Additionally, if your application vendor supports both SQL 2000 and MySQL, they likely have scripts or migration processes to go between platforms, so it may not hurt to ask, especially if you are unfamiliar with either side of the equation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.