Execute the following
Microsoft SQL Server T-SQL script in Management Studio Query Editor or imbed it in a stored procedure to start a job which is configured with SQL
Server Agent:
-- Execute system stored procedure
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 Agent job
CREATE PROC sprocStartMonthlyInventoryJob
AS
EXEC msdb.dbo.sp_start_job N'Monthly Inventory Processing';
GO
-- Execute t-sql stored procedure
EXEC sprocStartMonthlyInventoryJob
GO
Job related
T-SQL scripts:
-- 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 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
------------
-- 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
------------
------------
-- 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
*/
------------ |
