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 :). 

Parents
No Data
Reply
  • 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
Children
No Data