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?

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.

友情链接