SSRS Subscription emails are not being delivered (SharePoint Integration Mode)

Leave a comment

For the past few days we have had issues with subscriptions not being emailed to our end users. I had not experienced this issue before so I googled and found some posts on Dean Kalanquin’s blog that while helpful did not resolve the issue. They did, however, lead me in the right direction shich is why I posted them below.

Monitoring and Troubleshooting Subscriptions

Troubleshooting Subscriptions: Part II, Using the Reporting Trace Log File

Following the first blog I checked the SQL Agent job and all appeared to be fine according to the job’s history. Next, I looked in to the Events table and the Notifications table. To test the event I grabbed the execution code from the job properties, executed the code then queried the Events table repeatedly until I could see the entry. Crude, yes but I was in a pinch. 🙂

exec [SharePoint_ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=’ed209f73-953a-4fe8-96b6-3569bd2151aa’

SELECT * FROM Events

Following the execution I found that records were indeed being placed into both the Events table and the Notifications table as well. This proved that the event processing service was functioning correctly.

My next step was found in Dean’s second blog that lead me to the Trace Log file.

$:\Program Files\Microsoft SQL Server\MSRS10_50.MNPSRS11\Reporting Services\LogFiles\

There I found the error that was returned . .

Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.

This is when the lightbulb above my head lit up. 🙂  I had run across this issue before and it was permissions related. Below is my checklist that will quide you through fixing this issue without blindly applying sysadmin rights to you Report Server service account. 😉

  • Log in to your SQL Reporting Server server and view the configuration options under SQL Server Configuration manager. Once opened click on the SQL Services node and you will see the login used to run the service.
  • Navigate to the login in SQL Server, this under Security>Logins and add sysadmin permissions in Server Roles.
  • Run the subscription via SharePoint, Native Report Server or manually via execution code or SQL Agent job. If the subscription is sent, then the problem is permissions related. If not, hit the Back button and try another blog. 😀
  • Run the following code that grant execute permissions to several objects in the master and msdb databases. Simply Find/Replace [yourlogin] with your login.

GRANT EXECUTE ON msdb.dbo.sp_help_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_delete_job  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [yourlogin]

  • Add the public server role to the MSDB and Master databases under Login properties>User Mappings
  • In addition to the above you will need to GRANT SELECT permissions on ..

msdb.dbo.sysjobs
msdb.dbo.syscategories

Now all that is left is to remove the sysadmin server role and test your subscriptions again. All should be well, if not . . . well I can’t help you then. 😉

 

 

Custom Report Viewer Permission Level

Leave a comment

An issue that we have run into a few times with SSRS running in SharePoint Integration mode is that we would like for the end users to have access to the Report Services features without having god status or Full Control permissions in the library. To accomplish this a new permission level needs to be created specific to Reporting Services.

There are two ways to go about doing this at the permissions level an I am not a SharePoint guru so take this with a grain of salt. The first option is to create a permission level that only addresses the report viewing permissions. In this case you will need to couple this level with an additional standard SharePoint permission level for additional permissions. (Ex. Add Report Viewer and Design permissions, so the user can not only have Report Viewer permissions but also have Design permissions in the library as well. The second option is to combine two roles into one which is the approach I took. To accomplish this you will need to create the Report Viewer permission level and into it also add the features of another level. This is better illustrated in the below walkthrough.

On the Site Actions menu , click Site Settings.

1. On the Site Settings page, in the Users and Permissions section, click Advanced permissions.

2. If the Settings menu is not available, select Manage Permissions of Parent from the Actions menu. Otherwise, skip to step 4.

Note: The Settings menu is not available on the Permissions page if your site is inheriting permissions from its parent site.

3. On the Permissions page, on the Settings menu, click Permission Levels.

Click permission levels

4. On the Permission Levels page, click Add a Permission Level.

Add permission level

5. On the Add a Permission Level page, in the Name and Description section, type a name and optionally a description.

Add name description and permissions levels

In my case, I added the Report Viewer permissions as well as the View Only permission level permissions. To grab another roles permissions to copy them in to the new permission level simply click the permission level from the Permission Level page and copy the permissions to your new permission level.

*To add report subscription features simply check “Create Alerts” and “Manage Alerts” from within the permissions selection page.

Sharepoint 2010 SSRS URL’s for ASP.Net Reportviewer control

Leave a comment

So, you need to figure out the SQL Report Server URL parameters to pass to your ASP.Net reportviewer control and your Report Server is running in SharePoint mode?

After a lot of trial and error I came across this fix.

ASP.Net Reportviewer control properties

 

 

 

 

 

 

 

In order to connect to the report server you’ll need to add the following suffix to your Report Viewer properties > Report Server URL . . _vti_bin/ReportServer.

Example: http://siteurl/site/_vti_bin/ReportServer

You’ll also need the path to the report including the reportname.rdl

Example: http://siteurl/site/library/reportname.rdl

*If you have the proper permissions you can grab this path via the “Open with Explorer window”  (in Library Tools>Library) and simply add the /reportname.rdl 

Hope this helps someone out . . 🙂

SharePoint 2010 – Remove a Content Type from the New Document drop down

Leave a comment

While moving SSRS reports to our production environment I ran in to an issue with removing a particular content type from the New Document listing. I’m sure you can customize a view in some way to accomplish this . . or maybe not, but here is my solution. Head to the site affected and navigate to Library Tools>Library, then Library Settings.

Navigate to Library Settings

Once in Library Settings scroll down to Content Types and click “Change new button order and default content type”

Click "Change new button order and default content type"

All that remains is to “uncheck” the Content Type you would like to remove visibility for.   Bingo!

Change the visibility property of the content type.

Sharepoint 2010 – Add Document properties drop down

Leave a comment

The document drop down comes in handy when working with documents via version control (checking in&out) as well as working or editing reports in SSRS from a SQL Report Server running in SharePoint mode. Here is how to add the document properties drop down to the column of our library.

Navigate to the library in question.

Click on Library in Library Tools, then select Modify View

 

 

 

 

 

Inside of the View Edit page add the Name (linked to document with edit menu) column. This column will add a Name column that also provides the drop down properties functionality as well.

 

 

 

 

 

 

 

Final Result

SharePoint 2010 add description to folder

1 Comment

SharePoint 2010 does not support adding descriptions to your folders. BIG BUMMER. There is, however, a way that metadata can be created and associated as a description of the folder via custom content types.

Navigate to Site Action in the upper left pane of the page and select Site settings.

 

 

 

 

 

 

 

 

 

 

 

 

 

From within the site setting page select Site Content Types.

 

 

 

 

 

 

 

 

Select Create to create a new content type

 

 

 

 

 

Click Add from new site column

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

From this page enter your column name that will be displayed as a column on the page followed by selecting the data type and format

 

 

 

 

 

 

 

 

 

 

 

 

 

Navigate to the library in which you would like to create a new folder with a description.

 

 

 

Select Advanced Settings

 

 

 

 

 

 

 

 

 

 

 

Enable Custom content types

 

 

 

Also from within Library settings click the Add from existing site content types under Content Types

 

 

 

 

Add your custom content type

 

 

 

 

 

 

To create a new folder with description simply click documents from Library Tools

Select New Document followed by clicking the new content type that you created

 

 

 

 

 

 

 

 

In the creation window type the Folder Name and Folder Description

To make the column visible go into Library Tools>Modify view and click the column name

 

 

 

 

 

 

 

%d bloggers like this: