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'
------------
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
*/
------------
No comments:
Post a Comment