Associating SSRS Scriptions to SQL Jobs

I’ve dealt with a lot of scenarios where I needed to troubleshoot and test a reporting services subscription simply because it failed. Most of the issues stem from the owner permissions, typos when it comes to the email addresses or because the person left the company and didn’t remove their subscriptions. Knowing where to look and how to associate the rather ugly naming convention of the sql jobs in reference to the subscriptions can eat up some valuable time. So let me save you some time by sharing a script I have used on numerous occasions.

The script joins a few ReportServer specific tables to sys.objects. I cannot recall if I had tested this against SQL 2005 but I do know it works well with SQL 2008 and it would be safe to say that it works with 2008 R2 as well. I am pretty certain it should execute without a hiccup on a 2005 instance. I haven’t tested against 2012 as of yet, but when I do I’ll definitely update this post.

To bread and butter. Here’s the script. The following will return all SQL Jobs associated to SQL Server Reporting Services subscriptions.

SELECT
	cmd='EXEC msdb.dbo.sp_start_job @job_name= ''' + sj.[name] + ''';'
	,ReportName=c.[name]
	,ReportPath=c.[Path]
	,LoginName=u.[UserName]
	,SQLJobCreateDate=sj.[date_created]
FROM msdb.dbo.sysjobs sj
	JOIN [ReportServer].[dbo].[ReportSchedule] AS rs (NOLOCK) ON sj.[name] = CAST(rs.[ScheduleID] AS NVARCHAR(128))
	JOIN [ReportServer].[dbo].[Catalog] c (NOLOCK) ON rs.[ReportID] = c.[ItemID]
	JOIN [ReportServer].[dbo].[Users] u (NOLOCK) ON c.[CreatedByID] = u.[UserID]
ORDER BY sj.[date_created] DESC

I like to know what was created today so I run the following:

SELECT
	cmd='EXEC msdb.dbo.sp_start_job @job_name= ''' + sj.[name] + ''';'
	,ReportName=c.[name]
	,ReportPath=c.[Path]
	,LoginName=u.[UserName]
	,SQLJobCreateDate=sj.[date_created]
FROM msdb.dbo.sysjobs sj
	JOIN [ReportServer].[dbo].[ReportSchedule] AS rs (NOLOCK) ON sj.[name] = CAST(rs.[ScheduleID] AS NVARCHAR(128))
	JOIN [ReportServer].[dbo].[Catalog] c (NOLOCK) ON rs.[ReportID] = c.[ItemID]
	JOIN [ReportServer].[dbo].[Users] u (NOLOCK) ON c.[CreatedByID] = u.[UserID]
WHERE (CONVERT(VARCHAR(10),sj.[date_created],101) = CONVERT(VARCHAR(10),GETDATE(),101))
ORDER BY sj.[date_created] DESC

The cmd column provides me the command needed to kick off the subscription. Typically most of the subscriptions are email based so if it hits my inbox I know I am good!

Advertisements

Get Last Backup Date

There are many ways to go about getting the last backup date for any and all database but I tend to lean towards set based methods over using the GUI for many reasons. One in particular is for the fact that I can turn set based into an actionable report since monitoring backups is important after all.

The method I use the most is the following because it only returns information about databases that currently exists. I really do not need to see information about what used to exist, but I will also show that example as well. Before I go on you might want to understand the anatomy of both the master.sys.databases catalog view and msdb.dbo.backupset table.

/* GET LAST BACKUP DATE FOR ALL EXISTING DATABASES */
SELECT d.name, MAX(b.backup_finish_date) 'Last Backup Date'
FROM master.sys.databases d
	JOIN msdb.dbo.backupset b ON d.name = b.database_name
	WHERE (b.type = 'D' OR b.type = 'I')
GROUP BY d.name
ORDER BY d.name	 

The results indicate (104 row(s) affected). So I have 104 databases on this particular instance.

This is the example of returning the history for the last backup date which may contain information about past databases which depends on your maintenance for backup history. So this may vary.

/* GET LAST BACKUP DATE FROM BACKUPSET */
SELECT database_name, MAX(backup_finish_Date) 'Last Backup Date'
FROM msdb.dbo.backupset
WHERE (type = 'D' OR type = 'I')
GROUP BY database_name
ORDER BY database_name

The results indicate (108 row(s) affected) so between the two statements you can see there is a difference of four rows which is why I avoid using the msdb.dbo.backupset as the only source of record.

The easiest way to determine which databases no longer exists is to execute the following.

/* RETURN NON EXISTING DATABASE NAMES */
SELECT DISTINCT database_name 
FROM msdb.dbo.backupset
WHERE 
	(
		database_name NOT IN
			(
				SELECT name FROM master.sys.databases
			)
	)

The results indicate that (4 row(s) affected) and the names of the database that are not current.