How to run a query based on the foreach result set

Hi all, 

Let me first start off by saying I am a novice powershell user and I will greatly appreciate any help I can get. [:D]

Also I am using PowerShell as well as the SQLPSX addon. (Just seems a little easier to wrap my head around)

What I need to do, is run a query, and get the values from the results and pass them into subsequent queries and write each query result to a CSV. 

Up to this point I have been cruising. I am now at the piece where I need to take the results from query A and use the results to power queries B+. 

 

#Get the distinct KeyValues to run the individual reports from
$SQLQuery3 = "SELECT DISTINCT KeyValue FROM forensics"
$results3 = Get-SQLData -sqlserver $SQLServer -dbname $SQLDatabase -qry $SQLQuery3



foreach ($Obs in $results3) {
$SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$Obs'"
$results4 = Get-SQLData -sqlserver $SQLServer -dbname $SQLDatabase -qry $SQLQuery4
}

 

$SQLQuery3 produced 8 numeric values. I was hoping that the $Obs would pull in those individual values and run 8 seperate queries as to which I would have the results export to their own individual .CSV file. However when I am in powershellGUI it states that $Obs is getting the DataRow and not the specific individual values which is why the $SQLQuery4 cannot run. 

Is there any easy way to use foreach and have it report the data and not the datarow??

The main reason I am doing it this way, is I will be using this in unique environments where the KeyValues might not always be the same. 

Thanks in advance, and I apologize if I am not using the correct lingo :). 

  • What is the content of $results3.

    In other words, if you run this:

     

    $SQLQuery3 = "SELECT DISTINCT KeyValue FROM forensics"
    $results3 = Get-SQLData -sqlserver $SQLServer -dbname $SQLDatabase -qry $SQLQuery3
    $results3

     

    What is the output? (sanitise as necessary).

  • SeanQuinlan said:

    What is the content of $results3.

    In other words, if you run this:

     

    $SQLQuery3 = "SELECT DISTINCT KeyValue FROM forensics"
    $results3 = Get-SQLData -sqlserver $SQLServer -dbname $SQLDatabase -qry $SQLQuery3
    $results3

     

    What is the output? (sanitise as necessary).

     

    Thanks for the reply Sean

    The output from that query should show all of the distinct keyvalues from the forensics table which are in int format. The query results in SMMS would look something like this. The number of results could vary depending on the database that it is ran against.

     

    KeyValue

    ------------

    92

    109

    115089

    30

    73

    5589

     

    What I am expecting of the $Obs is that it would then run subsequent queries with the values given from the $results3.

    Example:

    With the results set above I would expect it to run a query for each result. The query without the variable would look like this.

    SELECT ObsValue FROM forensics WHERE KeyValue = '92'

    SELECT ObsValue FROM forensics WHERE KeyValue = '109'

    SELECT ObsValue FROM forensics WHERE KeyValue = '115089'

    etc....

    I was hoping the variable $Obs would pass those but it seems like it is passing the tabledata instead. Do you know of a way to do this?

     

  • Ok, I checked the help for Get-SqlData and it says it will return an array of System.Data.DataRow, so I guess it's doing what it should.

    Try updating this line:

     

    $SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$Obs'"

     

    To one of these:

     

    $SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$($Obs)'"
    $SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$($Obs.KeyValue)'"

     

    It might be that it still doesn't work due to the single quotes. PowerShell will generally treat everything within single quotes as literal and will not expand the variable.

    If so, one of these might work:

     

    $SQLQuery4 = ("SELECT ObsValue FROM forensics WHERE KeyValue = '{0}'" -f $($Obs))
    $SQLQuery4 = ("SELECT ObsValue FROM forensics WHERE KeyValue = '{0}'" -f $($Obs.KeyValue))

     

  • SeanQuinlan said:

    Ok, I checked the help for Get-SqlData and it says it will return an array of System.Data.DataRow, so I guess it's doing what it should.

    Try updating this line:

     

    $SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$Obs'"

     

    To one of these:

     

    $SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$($Obs)'"
    $SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$($Obs.KeyValue)'"

     

    It might be that it still doesn't work due to the single quotes. PowerShell will generally treat everything within single quotes as literal and will not expand the variable.

    If so, one of these might work:

     

    $SQLQuery4 = ("SELECT ObsValue FROM forensics WHERE KeyValue = '{0}'" -f $($Obs))
    $SQLQuery4 = ("SELECT ObsValue FROM forensics WHERE KeyValue = '{0}'" -f $($Obs.KeyValue))

     

     

    Sean, 

     

    I cannot thank you enough! You have made my day. This is the code that fixed my issue. Now it will run a select query based on the results of the previous query and I also used the same bit of code to write those results out to individual .csv files. 

    $SQLQuery4 = "SELECT ObsValue FROM forensics WHERE KeyValue = '$($Obs.KeyValue)'"
    Again, I appreciate your expertise!
    Thanks, 
    Kris