Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs

none
Populate a dataset in a foreach loop

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I am trying to populate a dataset in foreach loop, but I have difficulties getting it working.

    I have a set of plan_handles in a dataset. Now I want to go through them and execute a query to get the details about the plans. The result should get into a dataset (that I later will SqlBulkCopy into a database).

    I have this code. I can see that the queries are executed against the server, but when I look at $Plans.Tables[0] it only contains one row.

      foreach ($Plan in $PlanHandles.Tables[0])
  {
    $PlanHandle = $Plan["plan_handle"]
    
    $SqlSourceConnection.Open()
    $SqlCmdPlanGet = New-Object System.Data.SqlClient.SqlCommand("SELECT GETDATE() AS [timestamp], @plan_handle AS plan_handle, [dbid], objectid, number, encrypted, query_plan FROM sys.dm_exec_query_plan(@plan_handle)",$SqlSourceConnection)
    [void]$SqlCmdPlanGet.Parameters.Add("@plan_handle",$PlanHandle)
    $SqlCmdPlanGet.CommandTimeout = 30
    $SqlAdapterPlanGet = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmdPlanGet)
    $Plans = New-Object System.Data.DataSet
    $SqlAdapterPlanGet.Fill($Plans)
    $SqlSourceConnection.Close()
  }

    I have been reading about it and I understand, that it is something with that I should specify the datatable, but I am not sure how to get the code right.

    Ola Hallengren
    http://ola.hallengren.com

    Sunday, December 15, 2013 3:44 PM
    |
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    I was looking at $Plans.Tables[0] inside the loop. It seems like the dataset is overwritten every time.
    Sunday, December 15, 2013 5:24 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    I found out about this. I did a check if the variable exists.

        if (-not($Plans))
    {
      $Plans = New-Object System.Data.DataSet
    }

     
    Monday, December 16, 2013 1:43 PM
    |
    |