Query MS SQL and sent results to MY SQL DB

I need to create a script which will run on Windows 2003 and Windows 2008 servers to query the loca开发者_如何学Pythonlly installed MS SQL Server (2005 and/or 2008) and then write the results to a MYSQL DB on the Internet.

Any ideas how to create this? should I use vbscript? or a stored proc?

This is just the sort of thing SQL Server Integration Services was made for.

If you don't want to use middleware like Sql SIS then I guess you want to script something yourself.

I've seen a batch vbscript file used before now to load data between databases - but I wouldn't recommend it.

You could create a windows service that has database connections to Sql Server and MySql and reads from one and then writes into the other.

I guess you could export the data to CSV or Xml in a stored procedure and use xp_cmdshell to then move the data into a specific folder where it can be consumed by MySql (possibly by xp_cmdshell firing an executable) - although if your MySql instance is on the internet there would be security implications.

Depending on your version of Sql Server you could create a DTS job for this kind of thing. But Sql SIS does seem a better option if you want to use database middleware.

To be honest there are lots of ways to achieve this.