[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1657
  • Last Modified:

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:

$d.ExecuteWithResults($sqlQuery)

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.
0
dbbishop
Asked:
dbbishop
  • 3
  • 3
1 Solution
 
becraigCommented:
Here is a similar question I recently answered:

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28429089.html
0
 
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

0
 
becraigCommented:
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:
http://technet.microsoft.com/en-us/library/dd938892%28v=sql.100%29.aspx
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
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.
0
 
becraigCommented:
Sorry for being so late getting back I needed to validate most of this.

So SMO returns a dataset (like any other query method)
http://msdn.microsoft.com/en-us/library/ms205775.aspx

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

0
 
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.
0

Featured Post

A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now