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!

Intermittent Lockups with SSMS 2012

First off this post should have been pressed long ago (5/31/2012), but for whatever reason I merely saved it to draft rather than publish it. So without further ado…

Not long ago I started to encounter very intermittent lockups with SSMS 2012. I searched the web hi and low to see if anyone else had encountered this very problem, but I ended up with nothing remotely close. So I decided to turn to twitter and posted my issue to the sql community using the #sqlhelp hashtag.

It wasn’t long until I received a tweet from J. Verheul (‏@DevJef | Blog)

And that’s how our glorious conversation began… Then @DevJef mentioned he reinstalled SP1 for VS2010 and that cleared up his problem.

So I followed suit and applied SP1 for VS2010 and I am happy to report that I am no longer experiencing random lockups with SSMS 2012! Thank you @DevJef and thank you sql community for always being there!

Please visit Jef’s post (#SQLHelp – SQL 2012 Management Studio Freezes). His insight helped me tremendously and save me a lot of frustration and headaches!

Extending Recent Files List in SSMS 2012

If you are like me you save most (if not all) of the scripts or queries that you create. It is just a huge time saver and with our aggressive work load we need all the time we can spare. In my case I deal with a significant volume of production deployments/promotions so I burn through scripts frequently.

On a few occasions (when I need it most of course) the file names often drift from memory so I sometimes look to my recent file list in SSMS to help jog my memory. Yes we have deployment request which I can easily reference but this post is about SQL Server Management Studio, so there. 🙂

Back to the subject… by default SSMS 2012 will only display the last six but what if you want to extend that number to 10 or more?

Image

I’d like to point out that Jugal Shah (blog | @imjugal) published an article that covers SSMS 2000, 2005, 2008 and 2008 R2. Here is the link to his article if you would like to read his post Change Setting for Recently Used Files in SQL Server Management Studio SSMS.

The process is pretty much identical with SSMS 2012 and you go about it like so…

Step 1: Tools >> Options

Image

Step 2: Increase the value for: “items shown in recently used lists”

Image

Step 3: Let’s Verify

The change is immediate, so there’s no need to close and reopen SSMS.

Image

Recovering unsaved or modified scripts

Let’s face it we have all spent countless hours developing and/or polishing up our sql scripts deep into the wee hours of the night to the point of near exhaustion. We rise early the next morning (or a few hours later in most cases), pour a cup of coffee and head over to our laptop to find that our system rebooted. Then reality sinks in and you suddenly realize that you didn’t save anything before calling it a night. Now you’re thinking “[enter swear phrase of choice here]”!

Don’t panic…

Typically when you re-open SQL Server Management Studio you’re prompted with a nice little screen that politely asks if you would like to recover the selected files or queries. Which is extremely helpful, but what happens when you don’t get that prompt, what then?

The answer is easy enough and may require you to change your folder options to show hidden files and folders. In the event you find yourself in a similar situation simply navigate to (assuming you are running Windows 7) C:\Users\”[your username goes here]”\AppData\Local\Temp\ and look for files similarly named like the ones illustrated below.