Using Objects Arrays in PS

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

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

}

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

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)"
}

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