Invoke-Sqlcmd pain

This topic contains 2 replies, has 2 voices, and was last updated by  Audun 2 months, 3 weeks ago.

  • Author
    Posts
  • #94987

    Audun
    Participant

    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?

  • #95004

    Don Jones
    Keymaster

    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.

  • #95047

    Audun
    Participant

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

You must be logged in to reply to this topic.