Page 2 of 4

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

Posted: Thu Jun 14, 2018 7:42 am
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()
}

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

Posted: Thu Jun 14, 2018 8:28 am
by jvierra
Please use the JobTracker. It will do everything you want that is possible to do.

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

Posted: Fri Jun 15, 2018 3:57 am
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

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

Posted: Fri Jun 15, 2018 4:18 am
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
}

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

Posted: Fri Jun 15, 2018 5:22 am
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 12 times

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

Posted: Fri Jun 15, 2018 5:40 am
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.

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

Posted: Fri Jun 15, 2018 6:09 am
by jvierra
You have no timer event.

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

Posted: Fri Jun 15, 2018 7:22 am
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 274 times
2.JPG
2.JPG (84.04 KiB) Viewed 274 times

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

Posted: Fri Jun 15, 2018 7:27 am
by jvierra
Yes but are all of the events connected to something?

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

Posted: Fri Jun 15, 2018 8:06 am
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.