Invoke-Sqlcmd pain

PowerShell Help
1

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?

2

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.

3

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