SQL Jobs Report

By -





Hilighter On The Page








SQL Jobs Report

Code Below.


SELECT DISTINCT
[sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]

, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, CASE [sJHIS].run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS [LastRunOutcome]
,COALESCE(SUBSTRING(CAST([sJHIS].run_date AS CHAR(8)), 1, 4) + '/'
+ SUBSTRING(CAST([sJHIS].run_date AS CHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST([sJHIS].run_date AS CHAR(8)), 7, 2), '') AS [LastRun]
,COALESCE(SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 1, 4) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 7, 2), '') AS [NextRun]
,STUFF(STUFF(STUFF(next_run_time, 1, 0, REPLICATE('0', 6 - LEN(next_run_time))),3,0,':'),6,0,':') AS [NextRunTime]
, [sSVR].[name] AS [OriginatingServerName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]


FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
JOIN [msdb].[dbo].[sysjobhistory] AS [sJHIS]
ON [sJOB].[job_id] = [sJHIS].[job_id]

LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]

LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
WHERE ([sJOB].[name] LIKE 'NESI%' OR [sJOB].[name] LIKE 'NICE%' )
AND [sJHIS].run_date = (SELECT MAX(SJH1.run_date) FROM [msdb].[dbo].[sysjobhistory] SJH1 WHERE [sJHIS].job_id = SJH1.job_id)
--ORDER BY [JobName]

UNION
SELECT DISTINCT
[sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]

, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
,'N\A' AS [LastRunOutcome]
,'N\A' AS [LastRun]
,CASE [sJOBSCH].next_run_date
WHEN '0' THEN 'N\A'
ELSE COALESCE(SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 1, 4) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 7, 2), '') END AS [NextRun]

,STUFF(STUFF(STUFF(next_run_time, 1, 0, REPLICATE('0', 6 - LEN(next_run_time))),3,0,':'),6,0,':') AS [NextRunTime]
, [sSVR].[name] AS [OriginatingServerName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]


FROM
[msdb].[dbo].[sysjobs] AS [sJOB]

LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]

LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
WHERE ([sJOB].[name] LIKE 'NESI%' OR [sJOB].[name] LIKE 'NICE%' )
AND [sJOB].[name] NOT IN (SELECT
[sJOB].[name] AS [JobName]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
JOIN [msdb].[dbo].[sysjobhistory] AS [sJHIS]
ON [sJOB].[job_id] = [sJHIS].[job_id])









Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more