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 »

jvierra wrote: Sat Jun 16, 2018 12:29 pm or you must poll the table in SQS to get the info.

This should cause the call to wait until the backup has completed. I suspect that currently your job is completing almost instantaneously so there can be no progress output.
You are doing an incredible job. Thank you for your help. I do want to pull this information from a DMV.

Code: Select all

$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'"

The PowerShell Code I do have works, but I need to figure out two things, which I could not yet achive:

1. How to run two jobs simultaneously. Examples for this are suprisingly hard to find The SQL Query job needs a param $BackupSetNamecould so it returns the percentage of the correct backup job.
2. Display the result (percent) of the SQL Query in the richtextbox (or a progress bar) or whatever.
I figure

Code: Select all

$result= $results = Receive-Job -Job $Job -Keep| Select Percent_Complete
but I never managed to get this working either.
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 »

1. Display the result (percent) of the SQL Query in the richtextbox (or a progress bar) or whatever.

As I posted a very long time ago. What you are trying to do cannot be done with PowerShell.
1. How to run two jobs simultaneously. Examples for this are suprisingly hard to find The SQL Query job needs a param $BackupSetNamecould so it returns the percentage of the correct backup job.
Just start a second job at any time. The JobTracker can run many jobs simultaneously.
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 »

I just discovered that we can send async results back from a job. This can be retrieved during the "UpdateScript".

Code: Select all

$sb = {
    $Database ='master'
    $BackupSetName = 'testser33'
    $FilePath = 'd:\testdb\testdb.bkp'
    $Server = 'Alpha'
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup 
    $smoBackup.Action = 'Database'
    $smoBackup.BackupSetDescription = "Full (copyonly)backup of $Database"
    $smoBackup.BackupSetName = $BackupSetName
    $smoBackup.Database = $Database
    $smoBackup.Copyonly = $true
    $smoBackup.MediaDescription = "Full (copyonly)backup of $Database File = $FilePath"
    $smoBackup.CompressionOption = 1
    $smoBackup.PercentComplete = 10
    $smoBackup.Devices.AddDevice($FilePath, 'File')
    
    $complete = Register-ObjectEvent $smoBackup Complete -Action {
          $global:bRunning = $false
    }
    $percent = Register-ObjectEvent $smoBackup PercentComplete -Action {
        Write-Progress -PercentComplete $smoBackup.PercentComplete -Activity Backup -Status Running
    }
    $info = Register-ObjectEvent $smoBackup Information -Action {
          $global:bRunning = $false
    }
    $smoBackup.PercentCompleteNotification = 1
    $global:bRunning = $true
    $smoBackup.SqlBackupAsync($Server)
    
    while($bRunning){  # waits until the backup signals complete
       sleep -Milliseconds 200
    } 
}
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 »

That is great! Thank you! I will add this asap. All the best.
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