Sunday, February 12, 2012

Cannot run job created by SMO

I've create a SQL Agent job using C# SMO to process an Analysis Service Database (see code below). When I tried to start job from Management Studio, I get the following error message...any ideas?

TITLE: Microsoft.SqlServer.Smo

Start failed for Job 'Schedule Job OLAPProj'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&LinkId=20476


ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot start the job "Schedule Job OLAPProj" (ID 089BEE15-B060-466E-B59E-7F7DAB1EB8DB) because it does not have any job server or servers defined. Associate the job with a job server by calling sp_add_jobserver. (Microsoft SQL Server, Error: 14256)



Common.ServerConnection l_oServerConn = new Common.ServerConnection();

l_oServerConn.ServerInstance = txtServer.Text;

l_oServerConn.LoginSecure = true;

l_oServerConn.Connect();

Smo.Server l_oServer = new Smo.Server(l_oServerConn);

Agent.Job l_oJob = new Agent.Job(l_oServer.JobServer, "Schedule Job " + cboDB.Text);

l_oJob.IsEnabled = true;

l_oJob.Description = "Schedule Job Processing OLAP Cube: " + cboDB.Text;

l_oJob.OwnerLoginName = @."NT AUTHORITY\SYSTEM";

l_oJob.Create();

Agent.JobStep l_oJobStep = new Agent.JobStep(l_oJob, "Process " + cboDB.Text);

l_oJobStep.SubSystem = Agent.AgentSubSystem.AnalysisCommand;

string l_sCmd = @."<Process xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">";

l_sCmd = l_sCmd + "<Object><DatabaseID>" + cboDB.Text + "</DatabaseID></Object><Type>ProcessFull</Type>";

l_sCmd = l_sCmd + "<WriteBackTableCreation>UseExisting</WriteBackTableCreation></Process>";

l_oJobStep.Command = l_sCmd;

l_oJobStep.Server = txtServer.Text;

l_oJobStep.DatabaseName = @."master";

l_oJobStep.JobStepFlags = Agent.JobStepFlags.AppendToJobHistory;

l_oJobStep.OnSuccessAction = Agent.StepCompletionAction.QuitWithSuccess;

l_oJobStep.OnFailAction = Agent.StepCompletionAction.QuitWithFailure;

l_oJobStep.Create();


I have posted a sample here, which should clarify this. Please let me know if you have further issues after reading the sample.

|||Based on your sample, I've added the following which seemed to do the trick:

l_oJob.ApplyToTargetServer("(local)");

Initially I use "localhost\yukon" which is name of my instance but that failed and "(local)" worked. Thanks.

No comments:

Post a Comment