Saturday, 10 February 2007

Executing a job on a sql 2005 server in c#

I thought this would be easier than it was so here is a little post on how to do it!

I was trying to execute a job hosted on sql 2005, and find the result of it. This originally sprung out of trying to execute a SSIS Packaged on a sql server rather than a web server.

Unless I have seriously missed some thing the only way to do this is to add the ssis package as a job in sql, then execute it in code.

Firstly add the following references to your project:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.ManagementDTS
Microsoft.SqlServer.RegSvrEnum
Microsoft.SqlServer.SMO
Microsoft.SqlServer.SMOEnum
Microsoft.SqlServer.SQLEnum
Microsoft.SqlServer.SQLTask

I also added the following using statements:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;

Now to the code:


using (SqlConnection oConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
{
ServerConnection serverConnection = new ServerConnection(oConnection);
Server oSqlServer = new Server(serverConnection);
JobServer oAgent = oSqlServer.JobServer;
Job oJob = oAgent.Jobs[ConfigurationManager.AppSettings["JobName"]];
JobHistoryFilter oFilter = new JobHistoryFilter();
oFilter.JobName = ConfigurationManager.AppSettings["JobName"];
DateTime oLastRunDate = oLastRunDate = oJob.LastRunDate;
oJob.Start();
while (oLastRunDate == oJob.LastRunDat)
{
oJob.Refresh();
}
OutComeLabel.Text = oJob.LastRunOutcome.ToString();
}
This does not include a time out period, so be sure to add one in - and there is no error handling to try and keep the code as short as possible!

8 comments:

Javier said...

Hi,
I used your code and recieve a error:

SQL Agent is not supported on this edition of SQL Server


I use MSQL Express 2005

[email protected]

Anonymous said...

Thank you so much, that was very helpfull, its exactly what i was looking for...

Anonymous said...

int timeout = 3600;

//creation d'une connexion au serveur
Microsoft.SqlServer.Management.Common.ServerConnection con = new Microsoft.SqlServer.Management.Common.ServerConnection(serv_instance, login, pass);

//creation de l'objet serveur
Microsoft.SqlServer.Management.Smo.Server egbi2k5 = new Microsoft.SqlServer.Management.Smo.Server(con);

//execute le job si il existe.
if (egbi2k5.JobServer.Jobs.Contains(jobname))
{

DateTime oLastRunDate = egbi2k5.JobServer.Jobs[jobname].LastRunDate;

egbi2k5.JobServer.Jobs[jobname].Start();

int nb_sec = 0;
while (oLastRunDate == egbi2k5.JobServer.Jobs[jobname].LastRunDate)
{
Console.Write(".");
egbi2k5.JobServer.Jobs[jobname].Refresh();

if (nb_sec < timeout)
{
nb_sec++;
System.Threading.Thread.Sleep(1000);
}
else
{
Console.WriteLine("time out: le process est trop lent");
//envoi d'email
}
}

Console.WriteLine("fin");

return;
}
else
return;




that was perfect I was seeking the way to know when the job was finished and this is done in a lovely way!

Michael Mileos said...

Thanks, this is exactly what I was looking for. So, I should thank you for posting, and google for finding.

Navnath said...
This comment has been removed by the author.
shalini said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
domain hosting india

John Harrelson said...

This is very amazing blog and information provided by the article of this blog is really nice and useful and i would like to visit the blog again. Basic Web Hosting

mamatha said...

Thanks for sharing this amazing information,really its very interesting to read.
Forum hosting