Add SQL results to an array and reference it in Powershell

Christopher Jack 1,611 Reputation points


I have the following code,

  ## - Get SQL Server Table data:
$SQLServer = 'server';
$Database = 'database';

## - 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 = $("EXEC [zoomfs].[DPDHeaderExport]");
$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;

$defaultdata = $DataSet.Tables[0].rows[0].data

for($i=0;$i -lt $DataSet.Tables[1].Rows.Count;$i++)
  write-host "value is : $i $($DataSet.Tables[1].Rows[$i][0])"

It just seems to write the count of the number of rows which is 70 to the output. It does not do the loop through?

How do I reference a row and value in the dataset?

Any help appreciated

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,407 questions
Advanced system utilities to manage, troubleshoot, and diagnose Windows and Linux systems and applications.
1,103 questions
{count} votes