Using Objects Arrays in PS

Welcome Forums General PowerShell Q&A Using Objects Arrays in PS

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
5 years, 2 months ago.

  • Author
    Posts
  • #10296

    Participant
    Points: 0
    Rank: Member

    Using Invoke-SQLCMD, I get a single column list of values. The type is Object[].Array

    I then start a While Loop while there are objects in the array:
    WHILE ($ary.Item.Count > 0)
    {
    –Do some stuff–
    $ary = $ary | Where {$_.name -ne $chkParams.column1} | Select -Property name
    }

    This works fine until there is only 1 value left in the list. Then $ary is no longer an Array; it is just an object. So the $ary.Item.Count returns 0 even though there is still one value left in the list.

    Basically, I retrieve a list of values from a SQL Query. I want to iterate through the list, removing one at a time until there are none left then exit the loop.

    Can someone suggest a better way?

    Thanks

  • #10297

    Participant
    Points: 0
    Rank: Member

    Why do you want to remove items from the array?

    Why not get the number of items in the array and iterate of that or possible better still do
    foreach ($item in $ary){

    do stuff

    }

  • #10298

    Participant
    Points: 0
    Rank: Member

    Ok you are right. This is better. Now i'm having a syntax problem:

    I get data in two columns from a query. Now, for each one, i need to call a stored procedure, passing one of the columns as a parameter:

    foreach($record in $table)
    {
    Invoke-SQLCMD -ServerInstance "myServer" -database "myDatabase" -Query "EXEC sp_MyProcedure $record.column1"
    }

    But i am getting: Incorrect syntax near ' . '

    How can i do this?

    Thanks

  • #10299

    Member
    Points: 0
    Rank: Member

    When you want to access a property of an object while embedding it in a string (or evaluate the results of any other expression), you have to use the subexpression operator: $(). Try this:

    foreach($record in $table)
    {
        Invoke-SQLCMD -ServerInstance "myServer" -database "myDatabase" -Query "EXEC sp_MyProcedure $($record.column1)"
    }
    
  • #10300

    Participant
    Points: 0
    Rank: Member

    When in doubt always try to see what is happening with your substitution. So in this case change your code to

    foreach($record in $table)
    {
    $query = “EXEC sp_MyProcedure $record.column1″
    write-verbose $query
    Invoke-SQLCMD -ServerInstance “myServer” -database “myDatabase” -Query $query
    }

    You'd see that the query wasn't forming correctly. You could use write-debug instead of write-verbose if you preferred

The topic ‘Using Objects Arrays in PS’ is closed to new replies.