check and wait for sql agent job status smo c#

Question

Hi

im launching a dsql agent job via SMO JobServer.Jobs class and issuing job.Start() which works fine.

I then want to wait in a loop updating a textbox or label or something and inside the loop im checking the jobexecutionstatus is executing.

The problem is it just launches the job and never waits, has anybody else doen similar to this and can help please

Tuhin Paul · Answer

To check and wait for an SQL Server Agent job status using the SQL Server Management Objects (SMO) in C#, you can follow this approach. Essentially, after starting the job, you can repeatedly check the status of the job in a loop. You can update the UI elements like a textbox or label to show the job's status, but you also need to make sure you're not blocking the UI thread. using System; using System.Threading.Tasks; // For async operations using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Smo.Agent; // SMO Agent namespace using Microsoft.SqlServer.Management.Common; // For ServerConnection public class SqlAgentJobRunner { private Server _server; public SqlAgentJobRunner(string serverName) { // Connect to the server ServerConnection serverConnection = new ServerConnection(serverName); _server = new Server(serverConnection); } public async Task RunJobAndMonitorAsync(string jobName, Label statusLabel) { try { Job job = _server.JobServer.Jobs[jobName]; if (job == null) { throw new InvalidOperationException("Job not found."); } // Start the job job.Start(); statusLabel.Text = "Job started..."; // Wait for the job to complete by polling its status await MonitorJobAsync(job, statusLabel); } catch (Exception ex) { statusLabel.Text = $"Error: {ex.Message}"; } } private async Task MonitorJobAsync(Job job, Label statusLabel) { // Loop until the job execution completes while (true) { // Get the current job execution status job.Refresh(); // Refresh job status var jobStatus = job.CurrentRunStatus; switch (jobStatus) { case JobExecutionStatus.Executing: statusLabel.Text = "Job is executing..."; break; case JobExecutionStatus.WaitingForWorkerThread: statusLabel.Text = "Job is waiting for worker thread..."; break; case JobExecutionStatus.BetweenRetries: statusLabel.Text = "Job is retrying..."; break; case JobExecutionStatus.Succeeded: statusLabel.Text = "Job completed successfully."; return; // Exit the loop case JobExecutionStatus.Failed: statusLabel.Text = "Job failed."; return; // Exit the loop default: statusLabel.Text = $"Job status: {jobStatus}."; break; } // Sleep for a few seconds to avoid excessive polling (adjust time as needed) await Task.Delay(3000); } } } The Server object is used to connect to the SQL Server and interact with the SQL Agent jobs. It connects via SMO using ServerConnection. The job.Start() method launches the job, and we then enter a loop to monitor its status. The MonitorJobAsync() method uses a while (true) loop to continuously check the job’s status until it completes or fails. The job.Refresh() call is important as it refreshes the job's status. The statusLabel.Text is updated with the current job status in the loop. Since this is done asynchronously, the UI won’t be blocked. A Task.Delay(3000) ensures that the status is polled every 3 seconds, but you can adjust this delay as per your requirements.

Perry Whittle · Answer

thanks, that works perfectly

Perry Whittle · Answer

Hi Tuhin thank you for the code listing, the Jobexecution status does not expose succeeded or failed. There are some other oddities such as with the await job sync call which i'll look into