Getting data from SQL Server 2008 with Powershell

Question

I'm having some difficulties getting data from a simple stored procedure in SQL Server working. I have a Powershell script that needs to get variables filled from 3 columns (the procedure just returns 1 row)

Here's what I have that isn't working. Somehow I'm not referencing the column value correctly. I've tried various methods, but usually get the error "Cannot index into a null array". I don't want to iterate through the resultset, I just want to directly set some values from the one row returned into variables

$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = `
  "Server=ServerName;Database=ShopDB;Integrated Security=True"
$sqlQuery = new-object System.Collections.Specialized.StringCollection
$sqlQuery.Add("JobSettingsGet")
$Resultset = $conn.ExecuteWithResults($sqlQuery)

# $UserName = $table.Rows(0).Columns(0) 
  # error - Method invocation failed because [System.Data.DataTable] doesn't 
  # contain a method named 'Rows'.
# $UserName = $table.Rows[0].Columns[0] 
  # error - cannot index null array
# $UserName = $table.Row(0).Column(0) 
  # error - Cannot index into a null array,  also Method invocation failed 
  # because [System.Data.DataTable] doesn't contain a method named 'Row'.
# $UserName = $table.Rows[0].Columns[1].Value 
  # error - cannot index null array

I'd like to use the column name if possible, too.

Any pointers?

thanks, Sylvia

Solution

You could use System.Data.SqlClient.SqlConnection instead of SqlServer.Management class

$conn = New-Object Data.Sqlclient.Sqlconnection`
    ("Data Source=DATABASE;Initial Catalog=master;Integrated Security=SSPI;")
$adapter = New-Object Data.Sqlclient.Sqldataadapter("exec sp_who2", $conn)
$set = new-object data.dataset
$adapter.fill($set)
$table = new-object data.datatable
$table = $set.tables[0]

In order to print the whole table, just use format-table

$table | ft -AutoSize

To get a column by name, use the foreach iterator and access note properties by name

$table | % {$_.login}

Ed: Here is the same query by using SqlServer.Management.

[void][Reflection.Assembly]::LoadWithPartialName`
   ("Microsoft.SqlServer.ConnectionInfo")
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = `
   "Server=ServerName;Database=ShopDB;Integrated Security=True"
$sqlQuery = new-object System.Collections.Specialized.StringCollection 
$sqlQuery.Add("exec sp_who2") 
$resultset = $conn.ExecuteWithResults($sqlQuery)

Access the results by picking the zeroeth resultset, zeroeth table, zeroeth row and item by name

$resultset[0].tables.Item(0).Rows[0].Item("Login")

Comments

[Sylvia]

Thanks for the info. I can connect to the db, and using the foreach iterator works. However, what I'd like to do is (since I know there's only 1 row with 3 columns) get the actual data into variables without iterating. Something like below (which gives the error "Cannot index into a null array") : $UserName = $table.rows[0].columns[0]

[vonPryz]

$table.rows[0] ought to be System.Data.DataRow which doesn't have Columns member. Try using item() method: $table.Rows[0].item("Login").

[Sylvia]

Thanks, it works now. Any comment on when it would be best to use sqldataadapter instead of the other data access methods (ExecuteWithResults, etc)