datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to start a SQL Server job from a stored procedure?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor or imbed them in stored procedures to start a job which is configured with SQL Server Agent and perform other job control related operations programmatically:

-- SQL Server start job from script - SQL Server job programmatically control

EXEC msdb.dbo.sp_start_job N'Weekly Accounts Payable Transaction History';

 

/**** To perform certain actions SQLAgentOperatorRole in msdb maybe required. ****/

-- Creating application stored procedure to start SQL Server Agent job

CREATE PROC sprocStartMonthlyInventoryJob

AS

EXEC msdb.dbo.sp_start_job N'Monthly Inventory Processing';

GO

-- Execute t-sql stored procedure

EXEC sprocStartMonthlyInventoryJob

------------

-- Create SQL Server Agent job start stored procedure with input parameter

CREATE PROC uspStartJob @JobName sysname

AS

DECLARE @ReturnCode tinyint -- 0 (success) or 1 (failure)

EXEC @ReturnCode=msdb.dbo.sp_start_job @job_name=@JobName;

RETURN (@ReturnCode)

GO

-- Execute t-sql stored procedure with parameter and return code

DECLARE @return int

EXEC @return=uspStartJob N'CheckandBackupInventoryDB'

SELECT 'Start job result' = @return

------------

 

SQL Server Agent Job related T-SQL queries and scripts:  

 

------------

-- List all jobs with basic info like job_id, name, etc.

------------

SELECT * FROM msdb.dbo.sysjobs ORDER BY name

/* job_id   originating_server_id   name  enabled     description start_step_id    

category_id owner_sid   notify_level_eventlog   notify_level_email     

notify_level_netsend    notify_level_page notify_email_operator_id     

notify_netsend_operator_id    notify_page_operator_id delete_level     

date_created      date_modified     version_number

EF74E83A-325E-489B-B52C-D1E7DF5A5508      0     syspolicy_purge_history 1    

No description available.     1     0     0x01  0     0     0     0     0     0     0     0     

2010-05-09 05:03:23.737 2010-05-09 05:03:24.110 5

*/

------------

-- STOP a currently executing job - sql server agent stored procedure

EXEC msdb.dbo.sp_stop_job N'NightlyInventoryFeed' ;

 

-- Get job INFO on application job - execute system stored procedure

EXEC msdb.dbo.sp_help_job

    @job_name = N'FinanceBackup',

    @job_aspect = N'ALL' ;

GO

-- SQL Server system job INFO - t sql stored procedure

EXEC msdb.dbo.sp_help_job

      @job_name = N'syspolicy_purge_history',

      @job_aspect = N'ALL' ;

GO

/* Partial results

 

job_id                                    originating_server name                enabled

4C004358-62E0-4933-9268-90F54EF36AA9      DELLSTAR\SQL2008  syspolicy_purge_history 1

 

step_id     step_name                                 subsystem

1           Verify that automation is enabled.        TSQL

2           Purge history.                            TSQL

3           Erase Phantom System Health Records.      PowerShell

 

schedule_id schedule_name                       enabled

8           syspolicy_purge_history_schedule    1

 

server_id   server_name enlist_date             last_poll_date    last_run_date

0     DELLSTAR\SQL2008  1998-11-13 00:00:00.000 1998-11-13 00:00:00.000 20090214

 

*/

 

Check running status of a job

-- Check run status of a job

-- SQL Server 2008 T-SQL - Running = 1 means currently executing

DECLARE @job_id uniqueidentifier = '4C004358-62E0-4933-9268-90F54EF36AA9' 

EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id

 

-- If current_execution_status=1 then RUNNING
EXEC msdb.dbo.sp_help_job  @job_id='0D823348-2DDC-4816-93EA-307D6E736437'
------------

------------

-- SQL Server Agent disable job

------------

EXEC msdb.dbo.sp_update_job

    @job_name = N'Back Up Database - AdventureWorks2008',

    @enabled = 0 ;

------------


------------
-- SQL Server basic job history information for rolling month

------------
SELECT
   JobName,
         RunTime,
         Duration,
         Status,
         Server
FROM     (SELECT JobName = job_name,
                 RunTime = run_datetime,
                 Duration = SUBSTRING(run_duration,1,2) + ':' +
                            SUBSTRING(run_duration,3,2) + ':' +
                            SUBSTRING(run_duration,5,2),
                 Status = run_status,
                 Server = [server]
          FROM   (SELECT j.name AS job_name,
                         run_datetime = CONVERT(DATETIME,RTRIM(run_date)) +
                         (run_time * 9 + run_time%10000 * 6 +
                          run_time%100 * 10) / 2160000,
                         run_duration = RIGHT('000000' +
                         CONVERT(VARCHAR(6),run_duration), 6),
                         run_status = CASE run_status
                                        WHEN 0 THEN 'Failure'
                                        WHEN 1 THEN 'Success'
                                        WHEN 2 THEN 'Retry'
                                        WHEN 3 THEN 'Cancelled'
                                        WHEN 4 THEN 'Running'
                                        ELSE 'Other: ' +
                                        Convert(VARCHAR,run_status)
                                      END,
                         server
                  FROM   msdb.dbo.sysjobhistory h
                         INNER JOIN msdb.dbo.sysjobs j
                           ON h.job_id = j.job_id
                  WHERE  step_id = 0) x) z
WHERE    DATEDIFF(mm,RunTime,getdate()) < 1

ORDER BY RunTime DESC
------------

The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!

------------

-- SQL Server Agent detailed job information - more columns can be added as needed

------------

SELECT job.name                                      AS JobName,

       job.job_id,

       job.enabled                                   AS JobEnabled,

       CONVERT(SYSNAME,SERVERPROPERTY('Servername')) AS Server,

       job.DESCRIPTION,

       job.notify_level_eventlog,

       job.notify_level_email,

       job.notify_level_page,

       job.notify_email_operator_id,

       job.date_created,

       cat.name                                      AS CategoryName,

       sch.next_run_date,

       sch.next_run_time,

       srv.last_run_outcome,

       srv.last_outcome_message,

       srv.last_run_date,

       srv.last_run_time,

       srv.last_run_duration,

       op.name                                       AS NotifyOperatorName,

       op.email_address,

       job.date_modified                             AS JobDateModified,

       ssch.name                                     AS ScheduleName,

       ssch.enabled                                  AS ScheduleEnabled,

       ssch.freq_type,

       ssch.freq_interval,

       ssch.freq_subday_interval,

       ssch.freq_subday_type,

       ssch.freq_relative_interval,

       ssch.freq_recurrence_factor,

       ssch.active_start_date,

       ssch.active_end_date,

       ssch.active_start_time,

       ssch.active_end_time,

       GETDATE()                                     AS ThisReportRunDate

FROM   msdb.dbo.sysjobs job

       INNER JOIN msdb.dbo.syscategories cat

         ON job.category_id = cat.category_id

       LEFT OUTER JOIN msdb.dbo.sysoperators op

         ON job.notify_page_operator_id = op.id

       LEFT OUTER JOIN msdb.dbo.sysjobservers srv

         ON job.job_id = srv.job_id

       LEFT OUTER JOIN msdb.dbo.sysjobschedules sch

         ON sch.job_id = job.job_id

       LEFT OUTER JOIN msdb.dbo.sysschedules ssch

         ON sch.schedule_id = ssch.schedule_id

ORDER BY JobName

/* Partial results

JobName                 job_id                                    JobEnabled  Server

syspolicy_purge_history EF74E83A-325E-489B-B52C-D1E7DF5A5508      1     HPESTAR\SQL2008

*/

------------

------------

-- SQL stored procedure simple syntax - create t sql stored procedure

------------

USE AdventureWorks2008;

GO

CREATE PROCEDURE sprocSalesYearXtabQuarter

AS

BEGIN

SET NOCOUNT ON -- turn off rows affected messages

SELECT YEAR = YEAR(OrderDate),

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ,OrderDate) = 1 THEN TotalDue

                                    END),1),'') AS 'Q1',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 2 THEN TotalDue

                                    END),1),'') AS 'Q2',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 3 THEN TotalDue

                                    END),1),'') AS 'Q3',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 4 THEN TotalDue

                                    END),1),'') AS 'Q4'

 

FROM  Sales.SalesOrderHeader soh

GROUP BY YEAR(OrderDate)

ORDER BY YEAR(OrderDate)

END -- sproc definition

GO

-- SQL test stored procedure with no parameters - sql execute stored procedure

EXECUTE [AdventureWorks2008].[dbo].sprocSalesYearXtabQuarter

/*

YEAR  Q1                Q2                Q3                Q4

2001                                      5,294,961.92      7,671,148.64

2002  6,678,449.12      7,430,122.29      12,179,372.04     9,798,486.39

2003  7,738,309.35      9,727,845.55      16,488,806.73     15,192,201.07

2004  12,824,418.47     16,262,217.91     50,840.63  

*/

------------

Related articles:

Running Jobs

SQL Server Agent jobs and user contexts

How to: Start a Job (SQL Server Management Studio)

How to run a SQL Server 2005 Integration Services package as a SQL Server Agent job step

How To: Run Sql Server Agent and Sql Server Jobs with least privilege in Sql Server 2005

SQL Server DBA Checklist

 

Exam Prep 70-461
Exam 70-461