Powershell - proper way to execute SQL query with multiple select statements and result tables

Question

I'm trying to execute an SQL query with few select statements, that returns multiple tables as a result. The problem is that I can't find a way to read and use the tables separately.

Expected results:

Actual results: (it is printed row by row)

Purpose: I've made a script that creates an empty excel file with multiple sheets and each of the sheets will be used to contain each resultset of the query. The only thing left is to put the needed text into the sheets. Here is my code for that part only:

$ConnectionString = "Data Source=...;Initial Catalog=...;User Id=...;Password=..."
$DBServerName = $ConnectionString.split('=')[1].split(';')[0]
$DBName = $ConnectionString.split('=')[2].split(';')[0]
$DBUser = $ConnectionString.split('=')[3].split(';')[0]
$DBPassword = $ConnectionString.split('=')[4].split(';')[0]

$CurrentFilePath = "C:\SQLqueryWithManyResultsets.sql"

$query = Get-Content -literalPath $CurrentFilePath | Out-String #getting the query string from file

$resultTables = Invoke-Sqlcmd -Query $query -ServerInstance $DBServerName -Database $DBName -DisableVariables -Password $DBPassword -Username $DBUser -ErrorAction Stop

foreach ($result in $resultTables) {
    $result | Format-Table  #where the magic happens
}

I've made a lot of research, but I cannot find a proper way to store and read the tables the way i need.

zstefanova · Accepted Answer · 2017-06-22 17:57:06Z

1

Try this:

Clear-Host;
$objConnection = New-Object System.Data.SqlClient.SqlConnection;
$objConnection.ConnectionString = "...";


$ObjCmd = New-Object System.Data.SqlClient.SqlCommand;
$ObjCmd.CommandText = "...";
$ObjCmd.Connection = $objConnection;
$ObjCmd.CommandTimeout = 0;

$objAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$objAdapter.SelectCommand = $ObjCmd;
$objDataSet = New-Object System.Data.DataSet;

$objAdapter.Fill($objDataSet) | Out-Null;

for ($i=0; $i -lt $objDataSet.Tables.Count; $i++) {
    Write-Host ($objDataSet.Tables[$i] | Format-Table | Out-String);
}


$query = $null;

$objDataSet = $null;

$objConnection.Close();
$objConnection = $null;

edited Jun 22, 2017 at 17:57

zstefanova

1,8312 gold badges28 silver badges42 bronze badges

answered Jun 22, 2017 at 8:30

Mit_

1681 silver badge12 bronze badges

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

Collectives™ on Stack Overflow

Powershell - proper way to execute SQL query with multiple select statements and result tables

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