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

TestIng from my mobile phone

Testing

User Permission for Creating Linked Servers

I created a stored proc that drops and creates linked servers. The following system stored procs are called in my this stored proc:
master.dbo.sp_dropserver
master.dbo.sp_addlinkedserver
master.dbo.sp_addlinkedsrvlogin

It works fine in my dev environment. But when I ran it on the prod db server, the following errors came up:
Msg 15247, Level 16, State 1, Procedure sp_dropserver, Line 20
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 29
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.

After looking around, I found out that the account that runs the stored proc needs the "ALTER ANY LINKED SERVER", "ALTER ANY LOGIN" permission and the "dbcreator" server role. These were not setup on the SQL authentication account that runs the stored proc. So I did the following permission granting in addition to manually set the "dbcreator" role.

USE master;
GRANT ALTER ANY LINKED SERVER TO WarehouseAdmin;
GO

USE master;
GRANT ALTER ANY LOGIN TO WarehouseAdmin;
GO

Now it runs fine.

Tips on Deploying and Scheduling SSIS 2008 Packages

1. Use Windows authentication.
2. Deploy as File System instead of MSDB.
3. The account that runs SQL Server Agent needs to have sufficient privileges if connection to remote database server is included in the package.
4. Expose as few item as possible in the configuration file.

To repeat rows with column headings for a table with row groups

To repeat rows with column headings for a table with row groups
________________________________________
1. In Design view, select the table. The Grouping pane displays the row groups.
2. On right side of the Grouping pane, click the down arrow, and then click Advanced. The Grouping pane displays static and dynamic tablix members for each group. You can only set properties on a static tablix member.
3. In the Row Groups pane, click the static tablix member for the row that you want to repeat. When you select a static tablix member, the corresponding cell on the design surface is selected, if there is one. The Properties pane displays the properties for the selected tablix member.
4. Set the KeepWithGroup property in the following way:
o For a static row that is above a group, click After.
o For a static row that is below a group, click Before.
5. Set the RepeatOnNewPage property to True.
6. Preview the report. If possible, the row repeats with the group on each vertical page that the row group spans.
The post is at:
http://msdn.microsoft.com/en-us/library/cc627566.aspx

TFS 2008 to TFS 2010 Report Migration Notes

1. SSAS 2008 doesn't support "-" anymore.
"
everal Team Foundation pre-upgrade reports, and specifically the Scenario Details and Unplanned Work reports, show one of the following errors when run on SQL Server 2008:

The set must have a single hierarchy to be used with the complement operator.

The above messages appear because the WHERE clause in the report query is using a minus or complement operator (-) to exclude a specific attribute from the query. For example, the Scenario Details report includes a WHERE clause with the following syntax:
"
More details at:
http://msdn.microsoft.com/en-us/library/ff452590.aspx#REMComplement