Recently one of our DBAs came to me and asked if I could make a script that would allow him to type the name of a script and then have it run against all of the SQL servers we have, catch the output, and then display a message should an error occur. Od course I said, "Sure!". I whipped up a script, which I’ll post here later, that did exactly what he wanted. I used his supplied OSQL command that included the -o option which saves the output to a text file. I then used the Scripting.FileSystemObject to open the log files and parse them for specific words. I then added it to a ErrorMessage string and at the end of the script displayed all servers that had received errors.
This is where I ran into issues. You see OSQL sometimes writes output as ANSI and sometimes as Unicode! I thought this was odd and tried several options, such as always write Unicode and forces vbscript to always read the file as Unicode. This is when I discovered vbscript and OSQL’s Unicode was based on a different standard. I was using the WScript.Shell object’s Run method to launch the OSQL command. So I changed tactics and used the Exec method instead. This allowed my to create an object out of the command that was running and return values like Status and ExitCode. Most importantly, I was able to use the StdOut.ReadAll() method to grab the output and write it to a logfile, thus solving my file format issues.
Now our script runs totally awesome and everyone is happy. I’ll try to post it Monday along with an update of the Server Inventory script. I changed the ChassisType table to show the string based value instead of trying to interpret the integer value and determine server\non-server hardware. What I am going to add to my website is the ability to uncheck certain types of system model to remove them from the drop downs and reporting. I also added a new table for Memory Location and added some field for MemoryCapacity and MemoryDetails to store the Tag property. This allows me to correctly assign memory to memory boards and report the correct number of free memory slots per board.
Sorry it was long between posts. I had a week and a bit of vacation in June, plus the fiscal year of the company I work for is drawing to a close and we are super busy!