Using Objects Arrays in PS

This topic contains 4 replies, has 3 voices, and was last updated by  Richard Siddaway 4 years, 2 months ago.

  • Author
    Posts
  • #10296

    Matthew Humphrey
    Participant

    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

    Richard Siddaway
    Moderator

    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

    Matthew Humphrey
    Participant

    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

    Dave Wyatt
    Moderator

    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

    Richard Siddaway
    Moderator

    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

You must be logged in to reply to this topic.