Multiple SQL Select Queries with Powershell

Question

searched a lot but couldn't find a solution to my problem. First I have a script from which i make a few querys through a local Veeam database. It has a few select querys but everytime I run it, it just gives me the results of the first one but not from the others. I just tried to make the code a little more skinny so maybe somebody could give me a tip I just get the results for $job and $space_backup. I don't want to have four different querys but if it's not possible I have to go with it. I would appreciate any help.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dbServer;Database=$db;uid=$User;password=$Pass;"
$SQLConnection.Open()
$SQLCommand = $SQLConnection.CreateCommand()
$SQLCommand.CommandText = 
"SELECT TOP 1 stored_size AS size, job_name AS job FROM [VeeamBackup].[dbo].[ReportSessionView] ORDER BY [creation_time] DESC;"
"SELECT [free_space] AS [free] FROM [VeeamBackup].[dbo].[BackupRepositories] WHERE (name != 'Default Backup Repository');"
"SELECT  SUM(backup_size) AS backupsize FROM [VeeamBackup].[dbo].[WmiServer.RestorePointsView];"
"SELECT TOP 1 reason AS Reason, stop_details AS Detail FROM [VeeamBackup].[dbo].[Backup.Model.JobSessions] ORDER BY creation_time DESC;"
$SQLReader = $SQLCommand.ExecuteReader()
foreach($SQLCommand in $SQLReader.Read()){
    $space_backup = $SQLReader["size"]
    $job = $SQLReader["job"]
    $space_free = $SQLReader["free"]
    $space_all = $SQLReader["backupsize"]
    $reason = $SQLReader["Reason"]
    $detail = $SQLReader["Detail"]
    $SQLReader.Close()
}

Run each query separately.

Gordon Linoff
– Gordon Linoff

2016-02-02 12:00:18 +00:00
Commented Feb 2, 2016 at 12:00 — Gordon Linoff
– Gordon Linoff, Commented Feb 2, 2016 at 12:00
any other ideas instead of this unhelpful comment?

JudgeFudge
– JudgeFudge

2016-02-02 13:50:26 +00:00
Commented Feb 2, 2016 at 13:50 — JudgeFudge
– JudgeFudge, Commented Feb 2, 2016 at 13:50

Bruce · Accepted Answer · 2016-02-02 16:58:51Z

4

This should work for you. Just add the SqlDataAdapter and DataSet in place of your $SQLReader = $SQLCommand.ExecuteReader() command and make the selects one long string. I ended each of mine while testing this with a ; .

$readAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$readSet = New-Object System.Data.DataSet
$readAdapter.SelectCommand = $SQLCommand
$readAdapter.Fill($readSet) |out-null
$SQLConnection.Close()
Foreach ($row in $readSet.Tables[0].rows) {
   Write-Output "Do what you want with the first query results here"
   Write-Output "$($row.size)  $($row.job)"
}
Foreach ($row in $readSet.Tables[1].rows) {
   Write-Output "Do what you want with the second query results here"
   Write-Output "$($row.free)  "
}

Keep adding a Foreach for each query

edited Feb 2, 2016 at 16:58

answered Feb 2, 2016 at 15:28

Bruce

1,6231 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JudgeFudge Over a year ago

Doesn't work as I imagined, when I try it in PS i get a new table with the input from "free" but it won't give me anything in the variable $free, same with other foreach. It won't fill the table.

JudgeFudge Over a year ago

Got the mistake had to put the $row variable to $row2 and so on

Foreach ($row in $readSet.Tables[0].rows) {$space_backup = $row[0]; $job = $row[1]} Foreach ($row2 in $readSet.Tables[1].rows) {$space_free = $row2[0]} Foreach ($row3 in $readSet.Tables[2].rows) {$space_all = $row3[0]} Foreach ($row4 in $readSet.Tables[3].rows) {$reason = $row4[0]; $detail = $row4[1] }

works like a charm thank you!

Bruce Over a year ago

You're correct. I just edited my answer to add the $row.size. Apologies for the mistake.

Collectives™ on Stack Overflow

Multiple SQL Select Queries with Powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related