Post reply

Extract data into a HTML table from multiple sql servers using powershell

  • June 14, 2018 at 3:01 am

    #355320

    $Header = @"
    <style>
    TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
    TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
    TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
    </style>
    "@
    $serverList = Get-Content -Path "C:\Servers.txt"
    Get-WmiObject win32_Service -ComputerName $serverList | where {$_.DisplayName -match "SQL Server"} | select SystemName, DisplayName, Name, State, Status, StartMode, StartName |
    ConvertTo-Html -Property SystemName,Name,State,Status,StartMode,StartName -Head $Header |Out-File C:\EFGH_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).'html' | Sort-Object Name

    I was able to loop through servers and capture data into a single html file as above, but some servers are throwing out an error and the code still runs through the rest of the servers.txt file.How do I capture those servers in a separate text file or in the same html output file under a different table with a header:Servers unable to connect.
    I plan to run this under a task scheduled job.
    Thanks

  • June 19, 2018 at 11:33 am

    #1994497

    Would a try-catch construct help?

  • June 20, 2018 at 11:05 pm

    #1994760

    how do you do that?

  • June 21, 2018 at 4:56 am

    #1994785

    Something like

    try
    {
    Get-WmiObject win32_Service -ComputerName $serverList | where {$_.DisplayName -match "SQL Server"} | select SystemName, DisplayName, Name, State, Status, StartMode, StartName |
    ConvertTo-Html -Property SystemName,Name,State,Status,StartMode,StartName -Head $Header |Out-File C:\EFGH_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).'html' | Sort-Object Name
    }
    catch
    {
    write-host $_.Exception.Message
    #do something with the server(s) that are causing the error.
    }

    You may need to break up the command

Viewing 4 posts - 1 through 4 (of 4 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy