Useing 2000 to update a MySQL Database

Question

Useing 2000 to update a MySQL Database

JustABob

SSC Eights!

Points: 804
More actions
August 14, 2006 at 9:07 am

#61076

Does anyone have any experience connecting a MS SQL 2000 to a MySQL database? (Or know some good articles) I was planning on using a DTS package where I could query the MySQL db, and then add/update it based on the data that was in the 2000 db. This would be a process that would be run regularly, so I can't just do a one time export or some such thing.
I also have a 2005 SQL server available if that makes the connection easier, but I'm not as familiar with it.
Thanks!
Edit --
Never mind, I think I found a way to get around this problem, but if there is a way to do this I would still be interested in hearing about it.
August 17, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

I've never done this with MySQL, but have done it with DB2, Access, Excell, and Text Files. I would imagine if you can find an ODBC driver that will talk to MySQL, it would be just like any other DTS package.

As a last ditch effort, you can probably automate something on the MySQL side that spits out an XML or CSV file that your DTS package could suck in.

If you are copying a whole database over, I've not done much with MySQL, but I think if you do a standard MySQL backup, the backup file contains all of the SQL necessary to recreate the database and populate it. You could probably just execute that with SQL...I think the syntax should be compatable. But that may not be useful if you are pulling across data base on a query.

The Redneck DBA

sqltung Hall of Fame Points: 3962 More actions · Answer 2

You'll want to use the MySQL Connector/ODBC (also known as MyODBC) available here:

http://www.mysql.com/products/connector/odbc/

and setup a system DSN on the SS2000 server. Then you can connect with DTS.. OR you can setup the MySQL server as a linked server from SS2000 and query it from SQL.

JustABob SSC Eights! Points: 804 More actions · Answer 3

Thanks for the advice, never knew about the connector. And the linked server idea sounds pretty promising. If I have to switch back this sounds like the way to go.

Bob