PowerShell Query Using SMO to Return Results to an Array

I have a SQL query that returns a single column of data. I would like to put those results into a string array. I am running SQL Server 2005, so have to use SMO. The syntax I have to execute the query is:


but not sure how to get that into an array. I've been playing around with it quite a bit. If I add the .Tables property, I get a list of values from the query, complete with a header, but can't seem to get that information into an array.
LVL 15
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here is a similar question I recently answered:

dbbishopAuthor Commented:
I am using the code below to create the connection. From if, I have a database object named $db. A considerable amount of additional coding in the script requires the connection be established in this way, as I am scripting various objects in a large collection of databases. As such, your example would indicate another connection using another namespace, and I would prefer not to do this. I am using SMO to establish the connection. I cannot locate any correlation of your answer  to SMO.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
$ns = 'Microsoft.SqlServer.Management.Smo'
$svr = new-object ("$ns.Server") $server

Open in new window

I am guessing I missed the point of the other question which was I thought was the looping of the objects returned from the query.

If I am right and that is the case my example should have started you on the right path:
(Here is a snippet from an example)
$dtInventory = new-object "System.Data.DataTable" "dsInventoryData"
$dtInventory = $ds.Tables[0]
$dtInventory | FOREACH-OBJECT {[string]$_.ProductID + "," + $_.Name + "," + $_.ProductNumber + "," + $_.SafetyStockLevel + "," + $_.ReorderPoint + "," + $_.QOH

Open in new window

Which fundamentally follows the same as:
    $table = new-object system.data.datatable
    $adapter.Fill($table) | out-null
    $dirArray = @($table | select -ExpandProperty pathnamesX)

Open in new window

In my example I simply populated using the method used for connection which would be slightly different in your case.
Unless I am mistaken the first snippet is what you need to get you started, the only difference between the two examples would be the method of connection, which would not really matter once you are only worried about the population of the data into an array, which is shown in both examples above.
More details at:
How do you know if your security is working?

Protecting your business doesn’t have to mean sifting through endless alerts and notifications. With WatchGuard Total Security Suite, you can feel confident that your business is secure, meaning you can get back to the things that have been sitting on your to-do list.

dbbishopAuthor Commented:
Unless I am mistaken SMO does not give me an adapter and table objects (at least a table in this respect). SMO is not ADO.NET. It is a totally different flavor. For example, to return the results of a query, as in my example above, you use $result = $d.ExecuteWithResults($sqlQuery).Table, but now I need to get the values from each column, which is one of the properties of $result into an array.

I've already got a connection open to the database with SMO and wish to use it, not create a new connection so I can use ADO.NET objects.
Sorry for being so late getting back I needed to validate most of this.

So SMO returns a dataset (like any other query method)

You can then in turn loop through the Tables / rows / columns returned.

In your instance I would simply loop through the dataset and use an if statement to determine if $_.ColumnName matches the column I need, then simply populate an array with the value from that row - the page above has a working example.

The idea being you connect with SMO and use the ExecuteWithResults method
In theory (not tested as yet)
$ds | % {
   Foreach ($r in $_.Rows)
      Foreach ($c in $_.Columns)
       if ($c.ColumnName -eq "Column you need value for") {$newarray += $r.item}
          Write-Host $c.ColumnName "=" $r.Item($c)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
Sorry for my delay. Work and a bout with strep has had me down for a while.

Darn, was hoping there would be a way to move the result directly into an array without having to loop through the data. There is only one column in the results. Oh well, this is better than nothing, and would have been the way I'd have gone if it couldn't be done otherwise.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.