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.
User avatar
Pallas
Posts: 14
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

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()
}

User avatar
jvierra
Posts: 12874
Joined: Tue May 22, 2007 9:57 am
Contact:

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

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.

User avatar
Pallas
Posts: 14
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

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

User avatar
jvierra
Posts: 12874
Joined: Tue May 22, 2007 9:57 am
Contact:

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

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
}

User avatar
Pallas
Posts: 14
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

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.
BackDB_Jobs.psf
(166.29 KiB) Downloaded 12 times

User avatar
jvierra
Posts: 12874
Joined: Tue May 22, 2007 9:57 am
Contact:

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

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.

User avatar
jvierra
Posts: 12874
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Fri Jun 15, 2018 6:09 am

You have no timer event.

User avatar
Pallas
Posts: 14
Joined: Wed Jan 18, 2017 2:44 am
Contact:

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

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
1.JPG (104.75 KiB) Viewed 275 times
2.JPG
2.JPG (84.04 KiB) Viewed 275 times

User avatar
jvierra
Posts: 12874
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Fri Jun 15, 2018 7:27 am

Yes but are all of the events connected to something?

User avatar
jvierra
Posts: 12874
Joined: Tue May 22, 2007 9:57 am
Contact:

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

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.

Locked