Hi
I need to create a script which will run on Windows 2003 and Windows 2008 servers to query the locally 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.