SQL Server 2008 Email Notification Setup

I was trying to set up an email notification for a SQL Server agent job. I set up the Database Mail profile under Management, created operators under SQL server Agent, selected an operator under the notification section of a job. After the job is completed, guess what, there is no email. After diggin around I found this post(http://social.msdn.microsoft.com/Forums/en/sqltools/thread/839b5c27-9bc5-4467-a9d4-e78f9934a761). I tried restarting the agent serice and then realized that the mail profile was not enabled under SQL Server Agent Properties -> Alert System. After enabling that and restarting the agent service, it worked. The key here is to enable the mail profile first, then restart the agent service.

An Interesting Report Caching Issue

I was working on caching a report and encountered an issue. It simply didn’t work. All caching settings were tried. But it just doesn’t cache the damn report. I looked around on the Internet and no one seemed to have the same issue. One unique thing about this report is a big multi-valued parameter called Feature. The drop down has 150 – 200 entries depending on the selection of other parameters.

The next thing I did was looking into the [ExecutionLog2] view in the ReportServer database. I compared the parameters used in two different report runs. And I noticed that on the big list of features, one feature was converted to lower case (test feature) while the other report run has the same feature in regular cases (Test Feature). The data set that generates the feature list has the feature listed as Test Feature. And the lower case issue is on different feature everytime. I don’t know why SSRS converts it into all lower case. But because of that, it thinks these two report runs contain different parameter selections. Therefore it is not cached.

To fix that, I did a upper() on the query that generates the feature list and the query that generates the final report data. The report now takes a little longer to run. But the caching issue is fixed.

A good article I found when I troubleshoot the issue is at:
http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx

SSRS Report Caching

I came across a good article that talks about report caching.
http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx