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

SteveC · Accepted Answer · 2013-03-06 12:28:50Z

6

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

edited Mar 6, 2013 at 12:28

SteveC

17k25 gold badges116 silver badges180 bronze badges

answered Jan 4, 2012 at 12:53

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sylvia Over a year ago

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 Over a year ago

$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 Over a year ago

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

Collectives™ on Stack Overflow

Getting data from SQL Server 2008 with Powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related