Author Posts

September 21, 2013 at 10:56 am

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

September 21, 2013 at 11:05 am

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

}

September 21, 2013 at 5:06 pm

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

September 21, 2013 at 6:16 pm

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

September 22, 2013 at 1:49 am

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