I have 14 sql server 2000 instances. (Yes I know, old stuff and we should upgrade.)
Each server has an automated task to execute a stored procedure which writes to a text file a very simple report. The report is stored at c:\foo\report.txt.
I need to consolidate this to a single point and email the results to a distribution list.. How I do this is entirely up to me. I have both a unix and a Windows machine available to me that can talk to each sql server. I know perl. I know the db portion of sql server pretty well (strong Sybase background) but don't know much about SIS and some of the other cool tools in sql server.
My first task is to simply get the 14 text files and mail them out.
My second task is to take the text data (or the output from the stp) and create a summary table in another database (my preference would be mysql).
So how can I do this?
My first thought was a perl program (either windows or unix) using odbc to talk to the 14 servers, run the stp, suck up the data, store it in mysql, and generate the email. Is there a better way?