CODEWITHSUNDEEP
sql-serverpowershell
Xanderu
Xanderu

Reputation: 777

Using powershell to handle multiple SQL resultsets

I have several queries that I use for identifying issues in a SQL database but I'm trying to create at powershell script that I can use to do this automatically. The trouble I am having is that when I invoke my SQL scripts there are multiple result sets and my script only seems to capture the first set. I'm wondering what I need to do to cycle through all the results. This is the code with just some simple selects

$dataSource = 'Server'
$database = "DB"
$sqlcommand = @"
Select TOP 1000 * from tblA;
Select TOP 1000 * from tblB
"@


Function Convert-Dataset
{
    Param
    (
        [Parameter(Mandatory=$true)]
        $dataset
    )

    Begin
    {
        $return=@()
        For($r = 0; $r -lt $dataset.tables[0].rows.Count; $r++)
        {
            $table= new-object psobject
            If($dataset.tables[0].columns.ColumnName.Count -le 1)
            {
                $colName = [String]$dataset.tables[0].columns.ColumnName
                If($dataset.tables[0].rows.Count -eq 1)
                {
                    $colValue = [string]$dataset.tables[0].rows.$colName
                }
                Else
                {
                    $colValue = [string]$dataset.tables[0].rows[$r].$colName
                }
                $table | Add-Member -memberType noteproperty -Name $colName -Value $colValue
            }
            Else{
                For($c = 0; $c -lt $dataset.tables[0].columns.ColumnName.Count; $c++)
                {
                    $colName = [String]$dataset.tables[0].columns.ColumnName[$c]
                    $colValue = [string]$dataset.tables[0].rows[$r][$c]
                    $table | Add-Member -memberType noteproperty -Name $colName -Value $colValue
                }
            }
            $return +=$table
        }
    }
    End
    {
        Return $return
    }
}

$connectionString = "Data Source=$dataSource; " +
        "Integrated Security=True; " +
        "Initial Catalog=$database"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()
$return=Convert-Dataset -dataset $dataset
$return | Out-GridView

Upvotes: 4

Views: 3702

Answers (1)

Xanderu
Xanderu

Reputation: 777

I figured it out

$connectionString = "Data Source=$dataSource; " +
        "Integrated Security=True; " +
        "Initial Catalog=$database"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()

ForEach($table in $dataset.Tables)
{
        $table |Out-GridView -PassThru
}

Upvotes: 7

Related Questions