6

I am trying to run sp_AskBrent from PowerShell using Invoke-SQLCmd and capture its output in a variable:

$query = "EXEC SAIDBA.monitoring.sp_AskBrent @Seconds=10;"
$check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query 
             -ErrorAction Stop -ConnectionTimeout 3

When running with ExpertMode = 0, no problem.

But when running with ExpertMode = 1, we notice three things:

  1. It outputs every data to the shell
  2. $check is null
  3. It ends with the following error:

    Invoke-Sqlcmd: Duplicate column names are not permitted in SQL PowerShell. To repeat a column, use a column alias for the duplicate column in the format Column_Name AS New_Name.
    At line:1 char:18

    • $check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -Er ...
    • CategoryInfo: SyntaxError: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    • FullyQualifiedErrorId : DuplicateColumnNameErrorMessage, Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I think there is a workaround of providing parameters for sp_AskBrent to store the ExpertMode data into tables and select from those tables afterwards, but I want to make sure there is no way to get everything back at once in PowerShell.

  • If you go into the code of sp_askbrent and find the section starting with the line "ELSE IF @ExpertMode = 1" which is around 2000 lines into the code, you will find where it is selecting out that data. You could certainly change that so that it inserted those record sets (4 of them) into tables instead of selecting them out. – mskinner Nov 27 '15 at 16:36
  • 2
    @mskinner To be clear, the code licensing prohibits modification. (Why, you ask? Because we've had so many people bork up the code, and then the next guy calls us for support, and debugging somebody else's code is a giant pain in the rear - especially when they're complaining to their bosses that our code sucks, hahaha.) – Brent Ozar Nov 27 '15 at 19:57
  • This makes good sense and I should have read that explicitly before making any suggestions. – mskinner Nov 27 '15 at 20:08
7

Not to discount Brent's answer (although he does give some good ones on his training at times)...

In PowerShell you can execute queries that return multiple datasets, you just cannot do this using Invoke-Sqlcmd as it is not built for it currently.

You would have two options of doing this with either .NET native code (e.g. System.Data.SqlClient) or using the trusty SMO. I tend to chose SMO simply in the event I want to include server properties or something else that SMO has access to.

In the context of sp_AskBrent I went ahead and spent a few minutes just building the output into an HTML report. The main points of interest in this script is that to handle multiple datasets you will execute your query with ExecuteWithResults method which is available under the following namespaces:

  • Microsoft.SqlServer.Management.Smo.Server
  • Microsoft.SqlServer.Management.Smo.Database

You can see an example of using the database namespace on MSDN here. In my script I am using the Server namespace. It will work either way but if you review the MSDN article the ExecuteWithResults will return a DataSet object and that object will contain a DataTable. The number of DataTables is based on the number of datasets returned by your code. In the case of Brent's procedure you will get back 5 DataTables. You could verify this in the code below by adding in $results.Count, just before the foreach loop.

Now one other note in this script is I chose to output to HTML. You can chose another format if desired but outputting everything to the console is just not readable. I also will note I added help information so you can use Get-Help against the script if you need to see details on parameters or reminder yourself how to call it.

I used a little snippet of code from Pinal Dave to just generate some CPU usage on my local instance for this example. (Otherwise the procedure did not return much information).

enter image description here

enter image description here

Script

<#
    .SYNOPSIS
        Executes sp_AskBrent and outputs to HTML
    .DESCRIPTION
        Execute sp_AskBrent in normal or expert mode and outputs to an HTML.
    .PARAMETER server
        String. [REQUIRED] The SQL Server instance you wish to get results on.
    .PARAMETER timelimit
        Integer. Time used for @seconds parameter of sp_AskBrent
    .PARAMETER expertMode
        Switch. Just opts to have @ExpertMode = 1 for the query
    .PARAMETER sqlversion
        Integer. If on machine with multiple SQL Server tool versions installed, specify version.
    .PARAMETER outfile
        String. Set output file to generate HTML
    .EXAMPLE
    Run command in normal mode, which returns one dataset
    .\Script.ps1 -server MANATARMS\SQL12 -timelimit 5 -sqlversion 11 -outfile 'C:\temp\MyServer.html'
    .EXAMPLE
    Run command in expert mode, which returns multiple datasets
    .\Script.ps1 -server MANATARMS\SQL12 -timelimit 5 -expertMode -sqlversion 11 -outfile 'C:\temp\MyServer.html'
    .NOTES
    Does not check if sp_AskBrent is on the server before executing the query.
#>
[cmdletbinding()]
param(
    [Parameter(Mandatory=$true,Position=0)]
    [Alias("instance")]
    [string]
    $server,

    [Parameter(Mandatory=$false,Position=1)]
    [int]
    $timelimit = 10,

    [Parameter(Mandatory=$false,Position=2)]
    [switch]
    $expertMode,

    [Parameter(Mandatory=$false,Position=3)]
    [int]
    $sqlversion = 11,

    [Parameter(Mandatory=$false,Position=4)]
    [string]
    $outfile= 'C:\temp\Testing.html'
)

$HtmlTop = @"
<!DOCTYPE html>
<html>
<head>
<style>
    body { 
        background-color:white;
        font-family:Tahoma,Arial;
        font-size:10pt;
    }
    table {
        border-collapse: collapse;
    }
    th { 
        border:2px solid black;
        color:white;
        background-color: #0066FF;
        padding: 4px;
    }
        th.subhead {
            color:black;
            background-color: #8dbafc
        }
    td {
        border: 1px solid black;
        padding: 2px;
    }

</style>
</head>
<body>
<h2>Server Name: $($server)</h2>
<h3>Run Date: $(Get-Date)</h3>
"@

$HtmlTop | Out-File -FilePath $outfile -Force

if ($expertMode) {
    $askBrent = "EXEC sp_AskBrent @Seconds=$($timelimit), @ExpertMode=1"
}
else {
    $askBrent = "EXEC sp_AskBrent @Seconds=$($timelimit), @ExpertMode=0"
}

try { 
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=$($sqlversion).0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
}
catch {
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
}

$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server

$results = $srv.ConnectionContext.ExecuteWithResults($askBrent);

foreach ($t in $results.Tables) {
    $t | Select-Object * -ExcludeProperty RowError, RowState, HasErrors, Table, ItemArray | ConvertTo-Html -As Table -Fragment | Out-String | Out-File -FilePath $outfile -Append
    ## This is just to get a break between tables ##
    "<hr>" | Out-File -FilePath $outfile -Append
}
10

Brent here. Correct, @ExpertMode = 1 turns on multiple result sets - diagnostics, wait stats, file stats, and perfmon counters.

If you only want one result set, don't turn on @ExpertMode.

If you want multiple result sets, but your application (in this case, PoSH) can't consume them, you'll need to log them to tables. That's where these parameters come in:

  • @OutputDatabaseName - the name of the database you want to write into. Must already exist.
  • @OutputSchemaName - the schema where your tables will live, like dbo. Must already exist.
  • @OutputTableName - the table name for the top result set (diagnostics). This parameter also requires the above two. If the table doesn't already exist, it will be created.
  • @OutputTableNameFileStats, @OutputTableNamePerfmonStats, @OutputTableNameWaitStats - the rest of the result sets will be written here. If each table doesn't already exist, it will be created. Each of these is totally optional - you can pass in none, some, or all of them.

If you want it to work differently, describe what you need, and I'll see if I can come up with a way to get it. Hope that helps!

From a comment:

I was actually thinking about writing a health check script which I would run in case of incident or for monitoring and which would parse the output of sp_AskBrent.

OK, cool. Just a few things to keep in mind - the tables don't clean themselves out, and they can get big if you use the @SkipChecksQueries = 0 parameter, for example. The sp_AskBrent® licensing also prohibits distribution, so just make sure you're not bundling the script, or installing it on servers you don't own. Otherwise, go for it! If there's anything I can do to help make it easier, holler.

There's a date field in all of the output tables to help you maintain history tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.