Welcome to my first post of February 2012. Today's topic is the atrocious output sent forth by SQLCMD. For those accidental DBAs unfamiliar with SQLCMD, it's essentially the command-line interface with SQL Server, similar to using a New Query window in SSMS. It is the successor to OSQL, which was used with SQL Server 7 and 2000.
As seasoned DBAs will know, running a SQL query in SQLCMD and getting human-readable output can be very difficult. Consider the following example:
GETINFO.SQL
===========
SELECT ed.name AS 'Connection_Type', ec.encrypt_option AS 'Encrypted',
ec.auth_scheme AS 'Authentication_Method',
ec.client_net_address AS 'IP_Address',
(ec.net_packet_size*ec.num_reads) AS 'Bytes_Read',
(ec.net_packet_size*ec.num_writes) AS 'Bytes_Written',
(DATEDIFF(ss,ec.connect_time,SYSDATETIMEOFFSET())) AS 'Alive'
FROM sys.dm_exec_connections ec INNER JOIN sys.endpoints ed
ON ec.endpoint_id=ed.endpoint_id
ORDER BY Alive DESC
--(Note that SYSDATETIMEOFFSET() won't work on some versions, use GETDATE() instead).
Which renders as this:
While many developers have written parsers, particularly when accessing SQL Server as a service from an application layer (i.e. using ADODB or SQL Native Client connections), where does this leave your garden-variety DBA restricted for whatever reason to SQLCMD? What about retrieving output in CSV format for data analysis, or retrieving information on-the-fly that is readable and recognisable?
I faced this situation not long ago and resolved to do something about it. Although my solution is far from elegant - it involves procedural, not OOP, based programming - it works and takes a target server and query file as inputs, outputting a formatted CSV and the same information in a HTML file opened in your local browser. Also, it's based on technology available for any Windows XP + based system - the Windows Scripting Host, compiling VBScript - and the humble Windows batch file. There's a bit of string bludgeoning in there too.
OK, let's start at the beginning. Create a text file named 'go.bat' and put it in the directory of your choice (preferably an empty one):
GO.BAT
======
@echo off
set counter=0
cls
goto errorCatcher
:errorCatcher
IF "%1"=="" GOTO :wrong
IF "%2"=="" GOTO :wrong
set counter=0
goto main
:main
ping 127.0.0.1 > NUL
SQLCMD -S%1 -E -i%2 > results.tmp
CSCRIPT PARSER.VBS
TESTHTML.BAT
:wrong
echo Usage is go [SQL Server hostname\instance] [SQL script]
echo .
echo E.g. 'go localhost doSomething.sql 30'
echo Then the script specified is executed, once only, in the main window.
echo Output goes to OUTPUT.CSV
echo .
:eof
So what does this code do?
The constructor, for want of a better word, turns terminal output off and clears the screen. Then the :errorCatcher subroutine is invoked using the oldest of all old programming commands - GOTO. The :errorCatcher routine checks the input parameters. %1 is the target - if blank, catch the error by diverting to :wrong. %2 is the input file name. Counter is then reinitialised to 0. Next, :main introduces a brief wait (again for testing - REM this out if you like) before invoking SQLCMD and passing it the target server and input SQL file. Output is to the file results.tmp. The next step is to start the parser by passing the .vbs file to the WSH.
:wrong is simply a routine to catch an error and exit the script.
Assuming you run this with valid parameters, and all goes well, PARSER begins.
PARSER.VBS
==========
Set fs=CreateObject("Scripting.FileSystemObject")
Set inFile=fs.OpenTextFile("results.tmp")
Set outFile=fs.CreateTextFile("parsedResults.csv")
Dim nowChar,nameContainer,doneFlag,counter,prevChar,lcFlag
Dim headerArray()
nowChar=" "
prevChar=" "
counter=0
doneFlag=0
lcFlag=0
Call processHeaders
inFile.SkipLine
WScript.StdOut.WriteLine(" ")
Call processRows
Sub processHeaders
Do While inFile.AtEndOfLine <> True
nowChar=inFile.Read(1)
If nowChar<>" " Then
nameContainer=(nameContainer & nowChar)
doneFlag=0
ElseIf nowChar=" " And doneFlag=0 Then
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Or inFile.AtEndOfLine = True Then
outFile.Write(nameContainer)
lcFlag=1
End If
nameContainer=""
doneFlag=1
End If
Loop
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
outFile.Write(nameContainer)
lcFlag=1
End If
inFile.SkipLine
outFile.WriteLine("")
lcFlag=0
End Sub
Sub processRows
Do While inFile.AtEndOfStream <> True
nowChar=" "
counter=0
doneFlag=0
nameContainer=""
Do While inFile.AtEndOfLine <> True
prevChar=nowChar
nowChar=inFile.Read(1)
If nowChar="(" Then
Exit Do
End If
If nowChar<>" " Then
nameContainer=(nameContainer & nowChar)
doneFlag=0
ElseIf nowChar=" " And prevChar<>" " Then
nameContainer=(nameContainer & " ")
ElseIf nowChar=" " And prevChar=" " And doneFlag=0 Then
nameContainer=LTrim(RTrim(nameContainer))
If lcFlag=1 Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
outFile.Write(nameContainer)
lcFlag=1
End If
nameContainer=""
doneFlag=1
End If
Loop
If nowChar="(" Then
Exit Do
End If
nameContainer=LTrim(RTrim(nameContainer))
If lcFlag=1 Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
outFile.Write(nameContainer)
lcFlag=1
End If
inFile.SkipLine
outFile.WriteLine(" ")
lcFlag=0
Loop
End Sub
So this is a bit more complicated. Section by section:
Set fs=CreateObject("Scripting.FileSystemObject")
Set inFile=fs.OpenTextFile("results.tmp")
Set outFile=fs.CreateTextFile("parsedResults.csv")
Dim nowChar,nameContainer,doneFlag,counter,prevChar,lcFlag
Dim headerArray()
nowChar=" "
prevChar=" "
counter=0
doneFlag=0
lcFlag=0
This section simply constructs the variables we'll be using. Input/output files are handled using the Textstream object. headerArray() is a dynamic array NOT of type Array() but actually of WScript.Collection.
Call processHeaders
inFile.SkipLine
WScript.StdOut.WriteLine(" ")
Call processRows
This is fairly self-explanatory. First the sub-routine processHeaders is called, a bit of output formatting, then the second sub-routine processRows is called. Important difference between subs and functions - subs don't take parameters, functions do. But global variables (i.e. those defined outside the subs/functions) persist regardless.
Sub processHeaders
Do While inFile.AtEndOfLine <> True
nowChar=inFile.Read(1)
If nowChar<>" " Then
nameContainer=(nameContainer & nowChar)
doneFlag=0
ElseIf nowChar=" " And doneFlag=0 Then
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Or inFile.AtEndOfLine = True Then
outFile.Write(nameContainer)
lcFlag=1
End If
nameContainer=""
doneFlag=1
End If
Loop
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
outFile.Write(nameContainer)
lcFlag=1
End If
inFile.SkipLine
outFile.WriteLine("")
lcFlag=0
End Sub
OK - in English. While the file pointer is not at the end of the line, read into a variable nowChar one character from the input file (results.tmp). If the character is not a space, append the character to a string. If it is a space and the word is not marked as complete, take the string, trim it and add it to the dynamic array. Then output to file a comma, then the string. Then reset the string. There's an IF loop in there to detect if the word is the last in the file, if so don't put the comma in (so the row delimiter remains as /n, not ,).
Once the end of the line is reached, do the same with the last string in the variable lineContainer. Finally, reset the variables for the next sub.
Sub processRows
Do While inFile.AtEndOfStream <> True
nowChar=" "
counter=0
doneFlag=0
nameContainer=""
Do While inFile.AtEndOfLine <> True
prevChar=nowChar
nowChar=inFile.Read(1)
If nowChar="(" Then
Exit Do
End If
If nowChar<>" " Then
nameContainer=(nameContainer & nowChar)
doneFlag=0
ElseIf nowChar=" " And prevChar<>" " Then
nameContainer=(nameContainer & " ")
ElseIf nowChar=" " And prevChar=" " And doneFlag=0 Then
nameContainer=LTrim(RTrim(nameContainer))
If lcFlag=1 Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
outFile.Write(nameContainer)
lcFlag=1
End If
nameContainer=""
doneFlag=1
End If
Loop
If nowChar="(" Then
Exit Do
End If
nameContainer=LTrim(RTrim(nameContainer))
If lcFlag=1 Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
outFile.Write(nameContainer)
lcFlag=1
End If
inFile.SkipLine
outFile.WriteLine(" ")
lcFlag=0
Loop
End Sub
Very similar operation here, except this has to cope with multiple rows. By the way, the SkipLine operation between the subs is to skip the -------- line output by default in most SQLCMD outputs. Operation is the same, except the whole script is encapsulated in a Do While loop that checks to see if the file pointer is at the end of the file yet. There's a couple of minor differences too, by default the end of SQLCMD output has (x rows affected). There's another quick check in the code to see if ( is found, if so the line is skipped (by exiting the nested DO WHILE to the outer loop).
The output of this script, PARSER.VBS, outputs a valid .csv file with the headers comma-separated on the top row, values below. I.e:
Connection_Type,Encrypted,Authentication_Method,IP_Address,Bytes_Read,Bytes_Written,Alive
TSQL Local Machine,FALSE,NTLM,<local machine>,24576,24576,268
TSQL Local Machine,FALSE,NTLM,<local machine>,28672,24576,0
As you'll recall, in go.bat, TESTHTML.BAT is also called. You can bin this if you like, but it's a handy little scriptlet for writing the contents of the CSV out to a HTML table. You can replace this with a bit of custom PHP if you like:
TESTHTML.BAT
============
@echo off
ERASE OUTPUT.HTML 2>NUL
SET PATH=%PATH%;"C:\DOCUMENTS AND SETTINGS\DEL\LOCAL SETTINGS\APPLICATION DATA\GOOGLE\CHROME\APPLICATION"
cscript html_writer.vbs 1>NUL
chrome.exe file://<path>\output.html
You can see here I've adapted it to open in Chrome. Pick your favourite browser and change this accordingly. This invokes html_writer.vbs which directs standard output to NUL - this is because it's effectively a blind sub, I don't want any terminal output - only to file.
The erase command is just a bit of cleaning up.
HTML_WRITER.VBS
===============
Set fs=CreateObject("Scripting.FileSystemObject")
Set inFile=fs.OpenTextFile("parsedResults.csv")
Set outFile=fs.CreateTextFile("output.html")
Dim delim,charContainer,lineContainer,counter,wordArray()
delim=","
counter=0
Call writeHTMLHeader
Do While inFile.AtEndOfStream <> True
Do While inFile.AtEndOfLine <> True
charContainer=CStr(inFile.Read(1))
If charContainer=delim Then
Redim Preserve wordArray(counter)
wordArray(counter)=LTrim(RTrim(lineContainer))
lineContainer=""
counter=counter+1
ElseIf charContainer="<" Then
lineContainer=(lineContainer & "[")
ElseIf charContainer=">" Then
lineContainer=(lineContainer & "]")
Else lineContainer=(lineContainer & charContainer)
End If
Loop
Redim Preserve wordArray(counter)
wordArray(counter)=LTrim(RTrim(lineContainer))
Call writeToHTML(wordArray)
charContainer=""
lineContainer=""
counter=0
inFile.SkipLine
Loop
Call writeHTMLFooter
REM **********************************
Function writeToHTML(wordArray)
outFile.WriteLine("<tr>")
For Each i in wordArray
If i <> "" Then
outFile.WriteLine("<td>" & CStr(i) & "</td>")
End If
Next
outFile.WriteLine("</tr>")
End Function
REM **********************************
Sub writeHTMLHeader
outFile.WriteLine("<html><head></head><body>")
outFile.WriteLine("<table border=1>")
End Sub
REM **********************************
Sub writeHTMLFooter
outFile.WriteLine("</table>")
outFile.WriteLine("</body></html>")
End Sub
REM **********************************
Here we've got the usual VBScript constructor, followed by a call to the writeHTMLHeader function (which we can adapt as we see fit, writes the HTML header tags) - then some code to strip the data out of CSV and back into an array. Then for each member of the array, each value is written between <td> tags (the header function wrote the table headers). Finally, the footer is written. The batch file opens it in Chrome once done (change the <path>).
What does the output look like? This:
Usage: 'go <server\instance> <script>'.
Make sure all the files are in the same directory.
Comments welcome.
