The scenario was: We were called about SQL Server slowness. Tied up on other projects, we were not able to instantly verify performance. When we did check, about a half hour later, everything was fine. After a second call about the same issue a week later, we set up the trusty Profiler. The results: Zip, Nada, Rein, Nothing. Nothing abnormal occurred during the time span monitored. Different days were tested to no avail.

Not wanting to ignore the users, we wondered if the problem was too many users hitting the SQL Server at one time. Was there a simple way to instantly check user volume on the server? The stored procedure sp_who2 would answer the question. Unfortunately we were not always able to jump into our Query Analyzer to run sp_who2 instantly when the slowness was reported.

We needed something to give us a snapshot instantly of usage. The solution was to use VB scripting and dump the output into an Excel spreadsheet, Word document or WebPage. We chose sp_who2, but you can use sp_who (the field names are different).

We stored the vbs file and a batch file(sp_who2,vbs and sp_who2.bat) at the location we where working. Waited eagerly for the opportunity to trigger our program. When the call came, we were ready. The problem was one user updating the encyclopedia with full text indexing or that's what it seemed like. The user was contacted and we helped him to schedule his job during non peak, non production hours.

Hey! This was a good thing! We realized that our simple code could be used with almost any query or stored procedure. We could also output to Word, HTML, XML or whatever. Triggering it from a batch file means it is easy to schedule to run on boot up or anytime to pop up a snapshot of our SQL Servers.

The code for sp_who2.vbs is as follows:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i
' making the connection to your sql server 
' change yourservername to match your server
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
' this is using the trusted connection if you use sql logins
' add username and password, but I would then encrypt this
' using Windows Script Encoder
objConnection.Open _
    "Provider = SQLOLEDB;Data Source=yourservername;" & _
        "Trusted_Connection=Yes;Initial Catalog=Master;" 
        
' creating the Excel object application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
' The query goes here
objRecordSet.Open "sp_who2 ", _
        objConnection, adOpenStatic, adLockOptimistic
i = 1
objRecordSet.MoveFirst
Do Until objRecordset.EOF
i = i + 1
' This is setting the column names, font, colors, etc.
' This code can be simplified by ranging if desired.
objExcel.Cells(1, 1).Value = "SPID"
objExcel.Cells(1, 1).Font.Size = 10
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Interior.ColorIndex = 6
objExcel.Cells(1, 2).Value = "LOGIN"
objExcel.Cells(1, 2).Font.Size = 10
objExcel.Cells(1, 2).Font.Bold = TRUE
objExcel.Cells(1, 2).Interior.ColorIndex = 6
objExcel.Cells(1, 3).Value = "HOST NAME"
objExcel.Cells(1, 3).Font.Size = 10
objExcel.Cells(1, 3).Font.Bold = TRUE
objExcel.Cells(1, 3).Interior.ColorIndex = 6
objExcel.Cells(1, 4).Value = "DB NAME"
objExcel.Cells(1, 4).Font.Size = 10
objExcel.Cells(1, 4).Font.Bold = TRUE
objExcel.Cells(1, 4).Interior.ColorIndex = 6
objExcel.Cells(1, 5).Value = "STATUS"
objExcel.Cells(1, 5).Font.Size = 10
objExcel.Cells(1, 5).Font.Bold = TRUE
objExcel.Cells(1, 5).Interior.ColorIndex = 6
objExcel.Cells(1, 6).Value = "COMMAND"
objExcel.Cells(1, 6).Font.Size = 10
objExcel.Cells(1, 6).Font.Bold = TRUE
objExcel.Cells(1, 6).Interior.ColorIndex = 6
objExcel.Cells(1, 7).Value = "CPU TIME"
objExcel.Cells(1, 7).Font.Size = 10
objExcel.Cells(1, 7).Font.Bold = TRUE
objExcel.Cells(1, 7).Interior.ColorIndex = 6
' Now we are getting the data and highlighting certain columns
objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("SPID")
objExcel.Cells(i, 1).Font.Size = 10
objExcel.Cells(i, 1).Borders.LineStyle = True
objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("Login")
objExcel.Cells(i, 2).Font.Size = 10
objExcel.Cells(i, 2).Font.Bold = TRUE
objExcel.Cells(i, 2).Font.ColorIndex = 49
objExcel.Cells(i, 2).Interior.ColorIndex = 6
objExcel.Cells(i, 2).Borders.LineStyle = True
objExcel.Cells(i, 3).Value = objRecordset.Fields.Item("HostName")
objExcel.Cells(i, 3).Font.Size = 10
objExcel.Cells(i, 3).Borders.LineStyle = True
objExcel.Cells(i, 4).Value = objRecordset.Fields.Item("DBName")
objExcel.Cells(i, 4).Font.Size = 10
objExcel.Cells(i, 4).Borders.LineStyle = True
objExcel.Cells(i, 5).Value = objRecordset.Fields.Item("Status")
objExcel.Cells(i, 5).Font.Size = 10
objExcel.Cells(i, 5).Borders.LineStyle = True
objExcel.Cells(i, 6).Value = objRecordset.Fields.Item("Command")
objExcel.Cells(i, 6).Font.Size = 10
objExcel.Cells(i, 6).Borders.LineStyle = True
objExcel.Cells(i, 7).Value = objRecordset.Fields.Item("CPUTime")
objExcel.Cells(i, 7).Font.Size = 10
objExcel.Cells(i, 7).Borders.LineStyle = True
objExcel.Cells(i, 7).Interior.ColorIndex = 6

objRecordset.MoveNext

objExcel.Range("A1:G1").Borders.LineStyle = True

Loop
' automatically fits the data to the columns
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()
' cleaning up
objRecordSet.Close
objConnection.Close
The code for the sp_who2.bat batch file is as follows:
rem echo off
dbexcel2.vbs
exit

Copy and paste the two files. Before you run this you have to add your server name, and perhaps a username and password if your server requires same. You do not have to run only these columns from the sp_who2 query, you can run them all or just a single column. Just add or subtract the columns and fields as needed.

You can run your own query or another stored procedure. You will have to change the column and field object names to match you new query or stored procedure by running them in Query Analyzer in text mode so you can recover the column/field names. Likewise if you query a specific database you will have to change the line "Initial Catalog=yourdatabasename." You will of course need permissions to run these type of scripts, should try any new script in a test environment first and you should understand the impact of the script.

You can even give a script to a user to run as a batch file. This gets them data they need, when they need it. An example: The user needed to know if data was entered each evening from remote locations. They queried dates and location entries to get a listing of the top 10 dates. If a day was missed they then contacted the remote location with a request for data resend. All this might require you to get LAN Administration approval for and adding a limited access group membership. It may be worth the effort.

Caveats: When we tried to output to a Word document we found that the field DBName hung the report. So our fix for a Word report is to eliminate that field, until we find a fix. This is mentioned only to remind you that object oriented programs do not always play nicely.

References:

Adding formatted data to spreadsheet: http://www.microsoft.com/technet/scriptcenter/scripts/office/excel/ofexvb02.mspx

Connecting to SQL Server database: http://www.microsoft.com/technet/scriptcenter/scripts/misc/database/dbvb03.mspx

Windows Script Encoder: http://www.microsoft.com/downloads/details.aspx?FamilyId=E7877F67-C447-4873-B1B0-21F0626A6329&displaylang=en