How to run a Job from SQL Server Agent?

Viewed 7860

It is possible to execute a Job from SQL Server Agent in ASP.Net page triggered by clicking a button?

3 Answers

You can use sp_start_job (Transact-SQL)

Instructs SQL Server Agent to execute a job immediately.


[ @job_name= ] 'job_name'

The name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.

You can use it as a store procedure than run it in your code.

If your job runs a dts package, you can use Package.Execute method

Returns a DTSExecResult enumeration that contains information about the success or failure of the package execution.

Example from MSDN page;

    static void Main(string[] args)
    {
        Package p = new Package();
        p.InteractiveMode = true;
        p.OfflineMode = true;

        // Add a Script Task to the package.
        TaskHost taskH = (TaskHost)p.Executables.Add(typeof(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask).AssemblyQualifiedName);
        // Run the package.
        p.Execute();
        // Review the results of the run.
        if (taskH.ExecutionResult == DTSExecResult.Failure || taskH.ExecutionStatus == DTSExecStatus.Abend)
            Console.WriteLine("Task failed or abended");
        else
            Console.WriteLine("Task ran successfully");
    }

Use sp_start_job stored procedure.

EXEC dbo.sp_start_job N'YourJobName';
Related