Catching output from OSQL

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!

3 responses to “Catching output from OSQL

    • I’m really sorry! My old download section was on SkyDrive and WordPress doesn’t have a SkyDrive widget. I think I found the right script in my repository, but if you can tell me what you are trying to do maybe I can help customize it, or provide a better solution.

      • Thank you for your reply 🙂

        I would rather talk about it via e-mail. So if you could please contact me on my email. Can`t find yours 🙂

        I think you will like my idea for hta/sql application.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s