Some time back one of my DBA friend had came to me seeking a work around for making a documentation of the current users and process running in a Microsoft® SQL Server™ on a daily basis. I was able to come up with a satisfying solution to his problem (Obviously with the help of Google). So I would like to share the same with you guys.
Well, here we are going to see how we can use Excel application object to create a particular DBA documentation. “Sp_who2” is a command usually DBAs using to get the list of current users and processes running in an SQL Server instance. So what we have to do is get this information from SQL Server and store it in an Excel File for later presentation. When it comes to presentation we need to have a document with proper alignments and designs. So challenge is not lying in getting the data, in fact it’s lying in putting the document in proper formats. Fortunately “Excel Application” object is well capable of doing what we want.
We will use VB Script to do all the coding. To start we need to create an ADODB connection object and recordset as below.
CODE
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordSet = CreateObject("ADODB.Recordset")
Then we go for establishing a connection with SQL Server using the below connection string.
CODE
oConnection.Open "Provider = SQLOLEDB;Data Source=(Local);uid=sa;pwd=sa;Initial Catalog=Master;"
While creating the connection string you need to provide the SERVER NAME of your SQL Server, USER NAME and PASSWORD. In my code I have used the (local) as SERVER NAME and since I have not changed the user name and password for dbo.master it remains SA.
An Excel Application object has to be created next to do all our remaining exercises.
CODE
Set oExcel = CreateObject("Excel.Application")
Visible property of excel object will give you the freedom of making pop up the excel file after storing the information. For now we will make it false, because I don’t want it to be popped up.
CODE
oExcel.Visible = false
After creating the workbook and worksheet objects we will run our sql query (sp_who2) with the help of RecordSet object.
CODE
oRecordSet.Open "sp_who2 ", oConnection, adOpenStatic, adLockOptimistic
By putting the recordset to a loop we will extract the data and place it in an Excel file. oExcel.Cells(index, index) is having many use full properties for designing the excel file to a proper format. You can set attributes like font size, bold, color, line style etc. Once we finished getting the data, before saving we will use the below code to auto fit the columns based on the data available.
CODE
Set oRange = oWorksheet.UsedRange
oRange.EntireColumn.Autofit()
You can specify the path where you want excel to be saved.
CODE
oWorkbook.SaveAs "C:\Data.xls"
Well, one doubt can arise here. How we are going to execute this. It’s really simple, just put all the code in to a notepad file, save and rename the extension to “.vb”. Create one batch file with the following code. We had saved the vb file by giving the name “dbtoexcel.vbs”.
CODE
@echo off
dbtoexcel.vbs
exit
Just run the batch file, you will get “Data.xls” file under “c:\” with all the information regarding the current users and processes of an SQL Server. Isn’t that so simple. You can very well use this same method to get any information from any database.
Hope this will be use full to you.
Full Code Goes Here :
Save this code with the name "dbtoexcel.vbs"
CODE
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordSet = CreateObject("ADODB.Recordset")
' provide your servername and other credentials here.
oConnection.Open _
"Provider = SQLOLEDB;Data Source=(Local);" & _
"uid=sa;pwd=sa;Initial Catalog=Master;"
' creating the Excel application instance
Set oExcel = CreateObject("Excel.Application")
' making this option true will make excel file pop up.
oExcel.Visible = false
Set oWorkbook = oExcel.Workbooks.Add()
Set oWorksheet = oWorkbook.Worksheets(1)
' Provide the stored procedure for getting the active connection i.e. sp_who2
oRecordSet.Open "sp_who2 ", _
oConnection, adOpenStatic, adLockOptimistic
iCnt = 1
oRecordSet.MoveFirst
Do Until oRecordSet.EOF
iCnt = iCnt + 1
' you can set the column names here. For each column you can set font, colors, etc.
oExcel.Cells(1, 1).Value = "SPID"
oExcel.Cells(1, 1).Font.Size = 10
oExcel.Cells(1, 1).Font.Bold = TRUE
oExcel.Cells(1, 1).Interior.ColorIndex = 6
oExcel.Cells(1, 2).Value = "LOGIN"
oExcel.Cells(1, 2).Font.Size = 10
oExcel.Cells(1, 2).Font.Bold = TRUE
oExcel.Cells(1, 2).Interior.ColorIndex = 6
oExcel.Cells(1, 3).Value = "HOST NAME"
oExcel.Cells(1, 3).Font.Size = 10
oExcel.Cells(1, 3).Font.Bold = TRUE
oExcel.Cells(1, 3).Interior.ColorIndex = 6
oExcel.Cells(1, 4).Value = "DB NAME"
oExcel.Cells(1, 4).Font.Size = 10
oExcel.Cells(1, 4).Font.Bold = TRUE
oExcel.Cells(1, 4).Interior.ColorIndex = 6
oExcel.Cells(1, 5).Value = "STATUS"
oExcel.Cells(1, 5).Font.Size = 10
oExcel.Cells(1, 5).Font.Bold = TRUE
oExcel.Cells(1, 5).Interior.ColorIndex = 6
oExcel.Cells(1, 6).Value = "COMMAND"
oExcel.Cells(1, 6).Font.Size = 10
oExcel.Cells(1, 6).Font.Bold = TRUE
oExcel.Cells(1, 6).Interior.ColorIndex = 6
oExcel.Cells(1, 7).Value = "CPU TIME"
oExcel.Cells(1, 7).Font.Size = 10
oExcel.Cells(1, 7).Font.Bold = TRUE
oExcel.Cells(1, 7).Interior.ColorIndex = 6
' getting the data
oExcel.Cells(iCnt, 1).Value = oRecordSet.Fields.Item("SPID")
oExcel.Cells(iCnt, 1).Font.Size = 10
oExcel.Cells(iCnt, 1).Borders.LineStyle = True
oExcel.Cells(iCnt, 2).Value = oRecordSet.Fields.Item("Login")
oExcel.Cells(iCnt, 2).Font.Size = 10
oExcel.Cells(iCnt, 2).Font.Bold = TRUE
oExcel.Cells(iCnt, 2).Font.ColorIndex = 49
oExcel.Cells(iCnt, 2).Interior.ColorIndex = 6
oExcel.Cells(iCnt, 2).Borders.LineStyle = True
oExcel.Cells(iCnt, 3).Value = oRecordSet.Fields.Item("HostName")
oExcel.Cells(iCnt, 3).Font.Size = 10
oExcel.Cells(iCnt, 3).Borders.LineStyle = True
oExcel.Cells(iCnt, 4).Value = oRecordSet.Fields.Item("DBName")
oExcel.Cells(iCnt, 4).Font.Size = 10
oExcel.Cells(iCnt, 4).Borders.LineStyle = True
oExcel.Cells(iCnt, 5).Value = oRecordSet.Fields.Item("Status")
oExcel.Cells(iCnt, 5).Font.Size = 10
oExcel.Cells(iCnt, 5).Borders.LineStyle = True
oExcel.Cells(iCnt, 6).Value = oRecordSet.Fields.Item("Command")
oExcel.Cells(iCnt, 6).Font.Size = 10
oExcel.Cells(iCnt, 6).Borders.LineStyle = True
oExcel.Cells(iCnt, 7).Value = oRecordSet.Fields.Item("CPUTime")
oExcel.Cells(iCnt, 7).Font.Size = 10
oExcel.Cells(iCnt, 7).Borders.LineStyle = True
oExcel.Cells(iCnt, 7).Interior.ColorIndex = 6
oRecordSet.MoveNext
oExcel.Range("A1:G1").Borders.LineStyle = True
Loop
' here we are using the autofit function for arranging the columns
Set oRange = oWorksheet.UsedRange
oRange.EntireColumn.Autofit()
oWorkbook.SaveAs "C:\Data.xls"
oExcel.quit
oRecordSet.Close
oConnection.Close
Save this code with the name "sp_who2.bat"
CODE
@echo off
dbtoexcel.vbs
exit
Using VB Script for some DBA work arounds
10 Jul, 2008 - 07:28 AM
|