Author Posts

March 2, 2018 at 1:10 pm

Hi guys,
I'm struggling with the output from Invoke-Sqlcmd. If I run the query in MS SQL Management Studio, I get two rows as result from a table containing computernames.

If I put the query in $SQLselect and run the following code in PowerShell, the $SQLSelectResult variable will only contain one object with TypeName: System.Data.DataRow.

$SQLSelectResult = Invoke-Sqlcmd -Query $SQLselect -ServerInstance $SQLServer -Database $Database

I have also tried things like

Invoke-Sqlcmd -Query $SQLselect -ServerInstance $SQLServer -Database $Database |
     foreach {
       Write-Output $_.COMPUTERNAME
     }

with only one computername as result.

I just can't figure out how I can get the same result in SQL Management Studio and PowerShell. I think PowerShell always gives me the last row of the query.

Any suggestions how I can solve this?

March 2, 2018 at 3:08 pm

Invoke-SqlCmd is kind of a PITA. I've always just used .NET. Create a System.Data.Sql.SqlConnection, connect it to a SqlCommand with the query, execute the query and get a DataReader back. Enumerate the DataReader. If you look at our free ebook on building historical and trend reports (it's in the eBooks menu, here), you can see the 4-5 lines of code it takes to do that in the various examples.

March 3, 2018 at 7:43 am

Thank you, Don! I will definitely check that out!