ChristopherJack-1763 avatar image
1 Vote"
ChristopherJack-1763 asked ChristopherJack-1763 commented

Add SQL results to an array and reference it in Powershell


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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is the value of $DataSet.Tables[1].Rows.Count and what is the output of the script?

1 Vote 1 ·

Hi Ian.

write-host $DataSet.Tables[1].Rows.Count is outputting 0

It seems to show the result of the query which is 70 but DataSet does not seem to be getting set.

0 Votes 0 ·

To me it seems $SqlAdapter.Fill is not working.

I am getting no errors on the screen either.

0 Votes 0 ·

0 Answers