Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am
Post
by Pallas » Thu Jun 14, 2018 7:42 am
Thank you jvierra,
I fixed my errors that you pointed out. Working with the Button - Start Job was not getting me anywhere. So I returned to the sample code. Right now I am trying to run two jobs parallel. Asking SQL Server about the percentage of the backup job completion and write that to the rich text box.
I am stuck now on how to check the
second job (do while loop) for its status.
Any advice?
What I have so far:
Code: Select all
$buttonBackupDBJob_Click= {
#hier wird die eigentliche Sicherung per Knopfdruck durchgeführt
$Global:FileName = $textbox1_FN.Text
$Server = $global:MSSQLServer
$Database = $Global:SelectedDBName
$Path = $Global:Path
$Folder = $Global:CC + '\'
$FileName = $Global:FileName
$BackupSetName = $Global:BackupSetName
$Extension = '.bak'
$FilePath = $Path + $Folder + $FileName + $Extension
$textbox_path.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
$richtextbox_out.Text = "Display Information`r`n";
$SQLQuery = "SELECT Session_id as SPID, Command , query.text AS 'Query Text', Start_Time, Percent_Complete, dateadd(second,total_elapsed_time/1000, getdate()) as 'Total Elapsed Time',
dateadd(second,estimated_completion_time/1000, getdate()) as 'Estimated Completion Time'
FROM sys.dm_exec_requests request CROSS APPLY sys.dm_exec_sql_text(request.sql_handle) query
WHERE request.command in ('BACKUP DATABASE') and query.text like '%WITH name=%$BackupSetName'"
$jobSQLScript = {
Param (
$Server,
$Database,
$BackupSetName,
$SQLQuery
)
$myResult = (Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $SQLQuery).Percent_Complete
#$richtextbox_out.Text = $myResult
}
## - Setup job to execute in background:
## - SMO Backup Code below:
$jobScript = {
Param (
$Database,
$BackupSetName,
$FilePath,
$Server
)
#Load SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #SMO #das SMO Objekt holen und den Output nach /dev/null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
$smoBackup.BackupSetName = $BackupSetName
$smoBackup.Database = $Database
$smoBackup.Copyonly = $true #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
$smoBackup.MediaDescription = "Disk"
$smoBackup.CompressionOption = 1
#$smoBackup.PercentCompleteNotification = "10"
$smoBackup.Devices.AddDevice($FilePath, "File")
$smoBackup.SqlBackup($server) #Befehl der Sicherung wird ausgefuehrt
}
$argList = @(
$Database,
$BackupSetName,
$FilePath,
$Server
)
$argSQLList = @(
$Server,
$Database,
$BackupSetName,
$SQLQuery
)
$richtextbox_out.AppendText("Running Job - SMO Backup`r`n");
Start-Job -ScriptBlock $jobScript -Name SMOBackup1 -ArgumentList $argList
Start-Sleep -Seconds 5
Start-Job -ScriptBlock $jobSQLScript -Name SQLPercent -ArgumentList $argSQLList
## - Check for job completion:
do
{
$Counter = 1 + $Counter
$richtextbox_out.AppendText("Checking - still running`r`n")
$richtextbox_out.AppendText($Counter)
$richtextbox_out.ScrollToCaret()
Start-Sleep -Seconds 5
}
while ((Get-Job -Name 'SMOBackup1').State -ne 'Completed');
$result1 = Get-Job -Name 'SMOBackup1';
## - Display Job Status at EOJ:
[array]$SMOJobResults = (Get-Job -Name 'SMOBackup1' | Receive-Job);
## - Display Job Results in RichTextBox:
$richtextbox_out.AppendText("Displaying Asynch Job Results:`r`n");
$richtextbox_out.AppendText(($SMOJobResults | Format-Table | Out-String -Width 1000));
$richtextbox_out.ScrollToCaret()
$richtextbox_out.AppendText("Processing Completed`r`n");
$richtextbox_out.AppendText((Get-Date).ToString("dd_MMMM_yyyy_HHmm"));
$richtextbox_out.ScrollToCaret()
}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Thu Jun 14, 2018 8:28 am
Please use the JobTracker. It will do everything you want that is possible to do.
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am
Post
by Pallas » Fri Jun 15, 2018 3:57 am
Hi,
Job Tracker helps run the backup smoothly. And backup files are generated. But the script never enters the completedscript or updatescript. A breakpoint at
Code: Select all
Invoke-Command -ScriptBlock $psObject.UpdateScript -ArgumentList $psObject.Job
(function Update-JobTracker) is never reached when running "Debug". What am I doing wrong?
Code: Select all
$buttonStartJob_Click={
$buttonStartJob.Enabled = $false
$Global:FileName = $textbox1_FN.Text
$Server = $global:MSSQLServer
$Database = $Global:SelectedDBName
$Path = $Global:Path
$Folder = $Global:CC + '\'
$FileName = $Global:FileName
$BackupSetName = $Global:BackupSetName
$Extension = '.bak'
$FilePath = $Path + $Folder + $FileName + $Extension
$textbox_path.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
$richtextbox_out.Text = "Display Information`r`n";
$richtextbox_out.AppendText("Running Job - SMO Backup`r`n");
$SQLQuery = "SELECT Session_id as SPID, Command , query.text AS 'Query Text', Start_Time, Percent_Complete, dateadd(second,total_elapsed_time/1000, getdate()) as 'Total Elapsed Time',
dateadd(second,estimated_completion_time/1000, getdate()) as 'Estimated Completion Time'
FROM sys.dm_exec_requests request CROSS APPLY sys.dm_exec_sql_text(request.sql_handle) query
WHERE request.command in ('BACKUP DATABASE') and query.text like '%WITH name=%$BackupSetName'"
$argList = @(
$Database,
$BackupSetName,
$FilePath,
$Server
)
#Create a New Job using the Job Tracker
Add-JobTracker -Name 'BackDB' `
-JobScript {
Param (
$Database,
$BackupSetName,
$FilePath,
$Server
)
#Load SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #SMO #das SMO Objekt holen und den Output nach /dev/null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
$smoBackup.BackupSetName = $BackupSetName
$smoBackup.Database = $Database
$smoBackup.Copyonly = $true #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
$smoBackup.MediaDescription = "Disk"
$smoBackup.CompressionOption = 1
$smoBackup.Devices.AddDevice($FilePath, "File")
$smoBackup.SqlBackup($server) #Befehl der Sicherung wird ausgefuehrt
#--------------------------------------------------
}`
-CompletedScript {
Param ($Job)
$results = Receive-Job -Job $Job
$richtextbox_out.AppendText = $results
#Enable the Button
$buttonStartJob.ImageIndex = -1
$buttonStartJob.Enabled = $true
}`
-UpdateScript {
Param ($Job)
$results = Receive-Job -Job $Job -Keep
$richtextbox_out.AppendText = $results
$Counter = 1 + $Counter
$richtextbox_out.AppendText("Checking - still running`r`n")
$richtextbox_out.AppendText($Counter + ("`r`n"));
$richtextbox_out.ScrollToCaret()
#Animate the Button
if ($null -ne $buttonStartJob.ImageList)
{
if ($buttonStartJob.ImageIndex -lt $buttonStartJob.ImageList.Images.Count - 1)
{
$buttonStartJob.ImageIndex += 1
}
else
{
$buttonStartJob.ImageIndex = 0
}
}
}`
-ArgumentList $argList
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Fri Jun 15, 2018 4:18 am
The update script is called every time the timer ticks. Place a breakpoint in the time tick event.
$timerJobTracker_Tick={
Update-JobTracker #<--- breakpoint here
}
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am
Post
by Pallas » Fri Jun 15, 2018 5:22 am
The breakpoint at
Code: Select all
$timerJobTracker_Tick={
Update-JobTracker
}
is reached before the main form loads. NOT when the Job Button click event is raised. I am puzzled.
I attached the code because this seems very strange.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Fri Jun 15, 2018 5:40 am
If you used the "Start Job" custom control set this wouldn't happen.
Start with a new form. Drop the button on the form and run it. Click the button. It will work as expected.
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am
Post
by Pallas » Fri Jun 15, 2018 7:22 am
Not sure I follow. I see the exact same amount of events in my new form with Start Job as in my old form. Which event is missing?
1.JPG (104.75 KiB) Viewed 2534 times
2.JPG (84.04 KiB) Viewed 2534 times
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Fri Jun 15, 2018 7:27 am
Yes but are all of the events connected to something?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Fri Jun 15, 2018 8:06 am
What you are looking at is the code and not the linkage. The linkage is on each control. Be sure the code is linked.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked