Scripted Server Snapshot

Question

Scripted Server Snapshot

rscarlson

SSC Eights!

Points: 856
More actions
June 29, 2005 at 4:37 pm

#164769

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

rscarlson SSC Eights! Points: 856 More actions · Answer 1

Regarding the problem with outputing to Word. The DBName has nulls. We used an If..Then to detect the Null, and output "no db" else the name of the database. If anyone is interested I can e-mail the code.

hot2use SSCrazy Points: 2915 More actions · Answer 2

And adding a short inputbox for the server is cool if your centrally managing multiple servers.

[...]

Dim i

Dim srvname

srvname = InputBox ( "Enter the Server Name", "Server", "xxxxxx")

' 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=" & srvname & " ;" & _

"Trusted_Connection=Yes;Initial Catalog=Master;"

[...]

And this little addition formats the CPUTime column so that you can sort the column afterwards:

[...]

objExcel.Cells(i, 7).Value = FormatNumber(objRecordset.Fields.Item("CPUTime"), 0, -1, 0, 0)

[...]

Otherwise a cool piece of code. I don't have the time to get into coding much these days, but that might change.

Thanks

hot2use

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

RobertPaquette Old Hand Points: 307 More actions · Answer 3

I called objRecordSet rs and objWorksheet ws,

but it seems like you could just do something like this

For CurCol = 0 To rs.Fields.Count - 1: ws.Cells(1, CurCol + 1).Value = rs.Fields(CurCol).Name: Next

ws.Range("A2").CopyFromRecordset rs

Kenneth Lee Ten Centuries Points: 1293 More actions · Answer 4

Why do you have the banner (row 1) cells defined in the loop?

RobertPaquette Old Hand Points: 307 More actions · Answer 5

Just as the column names, for use with pivot tables. I use this code to automate reporting to excel. A few lines of code hoses the data to excel & refreshes all the pivot tables & graphs in the workbook...

rscarlson SSC Eights! Points: 856 More actions · Answer 6

rscarlson

SSC Eights!

Points: 856

July 13, 2005 at 6:34 am

#573839

Great ideas. Thanks.

rscarlson SSC Eights! Points: 856 More actions · Answer 7

rscarlson

SSC Eights!

Points: 856

July 13, 2005 at 6:40 am

#573842

You can remove the titles from the loop.

F Vandeputte Old Hand Points: 356 More actions · Answer 8

Hi,

This is really a nice script. I made a few changes. Now it works with any SELECT statement or stored proc.

I posted the code on my blog:

http://users.telenet.be/frederik.vandeputte/blog/2005/07/anyquery2excelvbs.html

Kind regards,

Frederik

RobertPaquette Old Hand Points: 307 More actions · Answer 9

you can replace the entire second loop with 1 command:

objWorksheet.Range("A2").CopyFromRecordset objRecordSet

F Vandeputte Old Hand Points: 356 More actions · Answer 10

You're righth Robert. Thanks for the tip.

I didn't like CopyFromRecordSet because I used to have lots of problems with it in earlier versions of Excel. Now I managed to create a +100 MB Excel file with Excel 2002. It looks like the limitations are gone.

The updated code can be found on

http://users.telenet.be/frederik.vandeputte/blog/2005/07/anyquery2excelvbs.html

Aaron West SSC Eights! Points: 808 More actions · Answer 11

I like the anyQuery script, Frederik, but here's a script with results more like the original.

'http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp

Dim srvname

If WScript.Arguments.count > 0 Then

srvname = WScript.Arguments(0)

Else

srvname = InputBox ( "Enter the server Name", "srvname", ".")

End If

Const adOpenStatic = 3

Const adLockOptimistic = 3

Dim i

' making the connection to your sql server

' change yourservername to match your server

Set conn = CreateObject("ADODB.Connection")

Set rs = 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

conn.Open "Provider=SQLOLEDB;Data Source=" & _

srvname & ";Trusted_Connection=Yes;Initial Catalog=Master;"

' creating the Excel object application

Set xl = CreateObject("Excel.Application")

xl.Visible = True

Set objWorkbook = xl.Workbooks.Add()

Set ws = objWorkbook.Worksheets(1)

' The query goes here

rs.Open "sp_who2 ", _

conn, adOpenStatic, adLockOptimistic

i = 1

rs.MoveFirst

' This is setting the column names, font, colors, etc.

' This code can be simplified by ranging if desired.

c = 1

If Not rs.EOF Then

For Each col In rs.Fields

ws.Cells(1, c).Value = col.Name

ws.Cells(1, c).Font.Bold = True

c = c + 1

ws.Cells(1, c).Value = "Query returned no results"

End If

ws.Range("A2").CopyFromRecordset rs

with ws

with .Range("A1:M1")

.Font.Bold = True

.Interior.ColorIndex = 6

end with

with .Range("A:M")

.Font.Size = 10

.Borders.LineStyle = True

end with

with .Range(.Cells(2, 3), .Cells(ws.UsedRange.Rows.Count, 3))

.Interior.ColorIndex = 6

.Font.ColorIndex = 49

end with

with .Range(.Cells(2, 8), .Cells(ws.UsedRange.Rows.Count, 8))

.Interior.ColorIndex = 6

.Font.ColorIndex = 49

end with

Const xlPart = 2

ws.UsedRange.Replace " "," ", xlPart

' automatically fits the data to the columns

ws.UsedRange.EntireColumn.Autofit()

ws.Range("B1").EntireColumn.Hidden = True

ws.Range("E1").EntireColumn.Hidden = True

'The following coverts column H from strings into numbers so that it is sortable:

'Some references for these pesky constants:

'http://mi4.com/blog/index.php?blog=5&p=28&more=1&c=1&tb=1&pb=1

'http://fox.wikis.com/wc.dll?Wiki~ExcelConstants~VFP

'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlhowConstants_HV01049962.asp

'http://www.p6c.com/CommonTypelibs/O2000_EXCEL9.html

Const xlDown = -4121

Const xlPasteValues = -4163

Const xlAdd = 2

ws.Range(ws.Range("H1"), ws.Range("H1").End(xlDown)).NumberFormat = "0"

ws.Range("H1").End(xlDown).Offset(1,0).Select

xl.Selection.FormulaR1C1 = "0"

xl.Selection.Copy

ws.Range(ws.Range("H1"), ws.Range("H1").End(xlDown)).PasteSpecial xlPasteValues, xlAdd, False, False

ws.Range("H1").End(xlDown).ClearContents

ws.Application.CutCopyMode = False

ws.Range("A2").EntireRow.Select

xl.ActiveWindow.FreezePanes = True

ws.Range("A1").Select

' cleaning up

rs.Close

conn.Close