Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Cross-Database Connectivity: An Answer For Kyle

Cross-Database Connectivity: An Answer For Kyle

  • Comments 1
  • Likes

Kyle Schoonover, a former colleague in MSIT, posed the following questions in a comment on October’s cross-version database attachments post:

Ward, I'm currently working in MySQL and writing conversion scripts to convert a MySQL database to a SQL 2005 database.  I have also been using OpenQuery to query through a linked server from SQL 2005 to MySQL.  I was wondering if you could comment on a way to possibly use DTS to transfer data from a database that is not readily supported like MySQL.

Also is there something better than using OpenQuery to create dynamic SQL to run against a linked server database that runs on MySQL.

It’s always great to hear from Kyle, who is a very sharp SQL guy.

Rather than using DTS for this purpose, Kyle, I suggest you look into SQL Server Integration Services under SQL Server 2005/SQL Server 2008.  It should be a trivial task to build a connection to your mySQL instance (ODBC, if all else fails); you’ll then have access to the full ETL capabilities of SSIS.  I’m aware people accessing Oracle and Teradata databases via SSIS using this technique, so I don’t think mySQL should be an issue.

To your second question, if you’re thinking of portable T-SQL code, I think OpenQuery is probably the best way to go.

Great to hear from you, Kyle, and I hope we can hook up next time I’m in the Pacific Northwest!

     -wp

Comments
  • This is a good answer to the question about converting data from MySQL to SQL Server, and then 5 minutes later I opened up the November issue of SQL Server Magazine to find the article "Integrate MySQL and SQL Servers" by Robert Sheldon which supplies all the details.  

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment