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:
- It outputs every data to the shell
$check
is null-
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.
Best Answer
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 trustySMO
. I tend to choseSMO
simply in the event I want to include server properties or something else thatSMO
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 withExecuteWithResults
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 theServer
namespace. It will work either way but if you review the MSDN article theExecuteWithResults
will return aDataSet
object and that object will contain aDataTable
. The number ofDataTables
is based on the number of datasets returned by your code. In the case of Brent's procedure you will get back 5DataTables
. You could verify this in the code below by adding in$results.Count
, just before theforeach
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).
Script