How to run sp_AskBrent with @ExpertMode = 1 in Powershell

  • Hi,

    I try to run sp_AskBrent from powershell using Invoke-SQLCmd and get back its output to 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 can 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 consisting on providing parameters for sp_AskBrent to store the expertmode data into tables and select on those tables afterwards, but I want to make sure there is no way to get everything back at once in powershell.

    Do you have any clue ?

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

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