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 » Sun Jun 17, 2018 5:02 am

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.

User avatar
jvierra
Posts: 13392
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 » Sun Jun 17, 2018 8:32 am

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.

User avatar
jvierra
Posts: 13392
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 » Tue Jun 19, 2018 2:58 pm

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
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 » Wed Jun 20, 2018 12:22 am

That is great! Thank you! I will add this asap. All the best.

Locked