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!