How to store a SQL Server query result to Powershell Array?

Question

I am new to PowerShell scripting and currently working on a script to load the result of SQL Server query to store as a PowerShell array. Below is my code for reference. :

 $SQLServer = 'MyServer';
 $Database = 'Test';
    
 ## - Connect to SQL Server using non-SMO class 'System.Data':
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
 $SqlConnection.ConnectionString = `
 "Server = $SQLServer; Database = $Database; Integrated Security = True";
    
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
 $SqlCmd.CommandText = $("select distinct Servername from dbo.tableA
where Servername like '%hw%'");
 $SqlCmd.Connection = $SqlConnection;
 $SqlCmd.CommandTimeout = 0;
    
 ## - Extract and build the SQL data object '$DataSetTable':
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
 $SqlAdapter.SelectCommand = $SqlCmd;
 $DataSet = New-Object System.Data.DataSet;
 $SqlAdapter.Fill($DataSet);
    
 $SqlConnection.Close()
 $Servername = @[SqlAdapter]

I expect $Servername to be an array having data elements store as "Server1', 'Server2', 'Server3', etc. based on the sqlquery result. I am planning to utilize $Servername array to loop through each server in future. For now, I am able to successfully connect to database, but I am still not able to get the query result to store in a PowerShell array. Can someone please guide on where I am making mistake?

from MS doc, Fill(DataSet) Adds or refreshes rows in the DataSet. (Inherited from DbDataAdapter), e.g. populate empty array, $colArry, with data from $DataSet. $colArry=@() $data = $DataSet.Tables[0] foreach ($colval in $data.Rows) {$colArry += $colval[1] $colArry += $colval[2] }. Hope this helps. — MZM
– MZM, Commented Dec 10, 2022 at 2:14
Try using following to see you dataset : $DataSet | Format-Table — jdweng
– jdweng, Commented Dec 10, 2022 at 9:54

mklement0 · Accepted Answer · 2022-12-10 19:48:08Z

$SqlAdapter.Fill($DataSet)

This fills the [System.Data.DataSet] instance stored in $DataSet with the query results, which is why you must use $DataSet to get the data you need (untested):

$serverNames = $DataSet.Tables[0].Server

.Tables[0] accesses the first and only [System.Data.DataTable] instance in the dataset containing the query results.
.Server retrieves the the values of the query result's Server column, courtesy of PowerShell's member-access enumeration.
- Note that this means that if there's only one result row, $serverNames will contain a single string rather than a single-element array containing that string.
- To ensure that an array is always returned, use
  $serverNames = @($DataSet.Tables[0].Server), or, with a (strong) type constraint,
  [string[]] $serverNames = $DataSet.Tables[0].Server

As for what you tried:

PowerShell statements only ever need to be separated with ; if they're placed on the same line, which means that all the ; instances in your code are unnecessary.
While $("select distinct Servername from dbo.tableA where Servername like '%hw%'") technically works, there is no reason to wrap a double-quoted string literal ("...") in the subexpression operator - just omit the $(...) enclosure.
As for $Servername = @[SqlAdapter]: perhaps that was just pseudo code, but, to be clear: @[...] isn't a valid syntax construct in PowerShell (at least as of PowerShell 7.3^[1]).

^{[1] By curious coincidence, @[...] just came up as potential future syntax for simplifying PowerShell's [pscustomobject] object-literal syntax - see GitHub issue #18747.}

Collectives™ on Stack Overflow

How to store a SQL Server query result to Powershell Array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related