Button - Start Job, how to multiple arguments, how to work with objects

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
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

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

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

Please use the JobTracker. It will do everything you want that is possible to do.
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

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

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

The update script is called every time the timer ticks. Place a breakpoint in the time tick event.

$timerJobTracker_Tick={
Update-JobTracker #<--- breakpoint here
}
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

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.
BackDB_Jobs.psf
(166.29 KiB) Downloaded 112 times
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

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.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

You have no timer event.
User avatar
Pallas
Posts: 14
Last visit: Fri Nov 23, 2018 4:24 am

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by Pallas »

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
1.JPG (104.75 KiB) Viewed 2517 times
2.JPG
2.JPG (84.04 KiB) Viewed 2517 times
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

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

Re: Button - Start Job, how to multiple arguments, how to work with objects

Post by jvierra »

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