Using SMO.Agent to retrieve SQL job execution status - security issue

Question

I've got a C# program that fires off SQL Server Agent jobs using the SQL Server Management Objects (SMO) interfaces. It looks something like:

Server ssis_server = new Server(
    new ServerConnection(SERVER_NAME, SERVER_USERNAME, SERVER_PASSWORD)
);

var agent = ssis_server.JobServer;
var ssis_job = agent.Jobs[job_name];

var current_status = ssis_job.CurrentRunStatus;

if (current_status == JobExecutionStatus.Idle)
{
    ssis_job.Start();
    OnSuccess("Job started: " + job_name);
}
else
{
    OnError("Job is already running or is not ready.");
}

I'm using SQL Server Authentication at this point to simplfy things whilst I work this out.

Now, my problem is that unless the SERVER_USERNAME is part of the 'sysadmin' dbo role, ssis_job.CurrentRunStatus is always 'Idle' - even when I know the job is running. It doesn't error out, just always reports idle.
If the user is an administrator, then the status is returned as expected.

Role membership you say?
Well, I added the SERVER_USERNAME SQL Server login to the msdb Role SQLAgentOperatorRole, that didn't seem to help.
The job's owner is a system administrator account - if that's the issue I'm not sure how to work around it.

Any ideas?

Cariecaries

Answer 1

You need to refresh the job by calling the Refresh() method on ssis_job before checking the status, then you will get the correct information.

Aliped

The Refresh method on the Job object fixed it for me. Thanks for pointing it out. – Fiedling

The Refresh() doesn't work for me if I'm calling it right after the Start(). It works when I'm debugging my code. Could it be a delay between the Start() and start of the sql job? – Eiderdown