Scripted Server Snapshot

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

  • 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.

  • 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.

  • 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

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

  • 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...

  • Great ideas.  Thanks.

     

  • You can remove the titles from the loop. 

     

  • 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

  • you can replace the entire second loop with 1 command:

    objWorksheet.Range("A2").CopyFromRecordset objRecordSet

  • 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

  • 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

        Next

    Else

        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

    end with

    Const xlPart = 2

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

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

    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

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

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