locked
How to display two result sets for two queries RRS feed

  • Question

  • The below is two queries for SQL Server where each runs and displays fine in Powershell when run as a single query setup in $query, but when I try to run and display both as shown below, only the first result is displayed.  The second result set just shows as empty space in the display panel.  I thought the last two lines in the script should display both result sets?  Thanks for any advice.

    $query = @"
      select * from t1
    select * from t2
    "@

    $SqlCmd.CommandText = $query
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $DataSet.Tables[0]
    $DataSet.Tables[1]

    Thursday, December 7, 2017 3:08 PM

All replies

  • Thursday, December 7, 2017 4:29 PM
  • Any chance you could advise more specifically.  I am newbie on DataSets and Powershell.  

    Thanks

    Thursday, December 7, 2017 4:49 PM
  • Use the C# code as a template for writing your code.  You need to use a reader to "Load" the dataset.

    $reader = $cmd.ExecuteReader()
    $dataset.Load($reader)


    \_(ツ)_/

    Thursday, December 7, 2017 5:05 PM
  • Here is a more complete example:

    $query = @'
        select * from contacts
        select * from testtext
    '@
    $connStr = 'Data Source=alpha\sqlexpress;Initial Catalog=LocalTest;Integrated Security=SSPI;'
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $query
    $ds = New-Object System.Data.DataSet
    $rdr = $cmd.ExecuteReader()
    $ds.Load($rdr, [System.Data.LoadOption]::OverwriteChanges, [string[]]@('Contacts', 'testtext'))
    $conn.Close()
    $ds.Tables['Contacts']
    $ds.Tables['testtext'] 


    \_(ツ)_/


    • Edited by jrv Thursday, December 7, 2017 5:59 PM
    Thursday, December 7, 2017 5:59 PM