Wednesday, February 15, 2012

Error adding a new job

I'm creating a new job and getting a failure when applying it to the server. When I script it is seems to work fine via SQL management studio. I haven't added any steps or a schedule yet, could this be the cause of the error the errors are a bit nebulous to say the least (hopefully the sql server errors will get passed through in next service pack)! Anyway heres the code, its a mixture of smo and my apps code:

//refresh jobs from server, releasing objects..

this.JobServer().Jobs.Refresh(true);

//create new job

m_oJob = new Agent.Job();

//loop through checking name (non case specific)

while(bExists==true)

{

n++;

bExists = false;

foreach(Agent.Job oJob in this.JobServer().Jobs)

{

if (oJob.Name.ToLower()==string.Format("[{0}] {1}",strName.ToLower() ,n.ToString()).ToLower() )

bExists = true;

}

}

m_oJob.Parent = this.JobServer();

//set name

m_oJob.Name = string.Format("[{0}] {1}",strName,n.ToString());

m_oJob.Description = String.Format ("Schedule for analytics job {0}",m_oScheduleJob.Name);

//set job owner to login

m_oJob.OwnerLoginName = m_oScheduleJob.Owner;

m_oJob.IsEnabled = true;

m_oJob.StartStepID = 1;

m_oJob.EventLogLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.OnFailure;

m_oJob.EmailLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.NetSendLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.PageLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.OperatorToEmail = "";

m_oJob.OperatorToNetSend = "";

m_oJob.OperatorToPage = "";

m_oJob.DeleteLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

if (!((Agent.JobServer)this.JobServer()).JobCategories.Contains("Analytics Job"))

{

Agent.JobCategory c = new Agent.JobCategory(this.JobServer(), "Analytics Job");

c.Create();

}

m_oJob.Category = "Analytics Job";

//assign to server

m_oJob.ApplyToTargetServer("myserver"); --> fails

m_oJob.ApplyToTargetServer("(local)"); --> fails

m_oJob.ApplyToTargetServer("myserver.dnssuffix"); --> fails

Ok figured out that you need to call the create before targeting server (should have looked at previous posts more closely, informative errors would have been good though - resorting to scripting and checking errors from sql for clues). Once saved need to call alter to change the objects however calling targetserver twice triggers an error anyone know how I can tell if its been called already?

|||I'm shooting from the hip here, so I will taking a closer look if I have a little more time, but I think that Job.EnumTargetServers() will do the trick. Let me know if that worked...|||Yes thats correct, thanks.

No comments:

Post a Comment