Showing posts with label SQL2012. Show all posts
Showing posts with label SQL2012. Show all posts

Friday, March 11, 2016

Reporting Server Subscription does not run on Schedule

I've setup a SSRS report that pulls data from two SQL Server databases across two different SQL Server instances. It runs under a SQL Server user and works like charm when run manually. However, when I try to setup a Subscription for this report, the report never runs according to the schedule. I don't even get an error in the Reporting Services log.

In order to understand what is going on, I need to go back to the SQL Server Agent and check the logs there. When you create a Report Server Subscription on a Schedule it creates a new Job for this Schedule and this Job initiates the Event that triggers the scheduled report(s) to run.You can read more about it here.

First I need to identify what Job ID is behind the schedule I want to investigate. The Jobs are created using a Unique ID (GUID) and in order to link the actual report to its schedule ID I run this SQL on the Reporting Server Database (Normally called "ReportServer_INSTANCENAME"):

select s.Description, us.UserName, s.LastStatus, s.LastRunTime, c.Path, uc.UserName, rs.ScheduleId,
from ReportServer.dbo.Subscriptions s
join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join ReportServer.dbo.Users us on us.UserID = s.OwnerId

Going back to the Job, I can see from the Job History it doesn't even get to the first step of the Job. Since this is AX, the Job is run under the same account as the Business Proxy Account. That is how SSRS normally is configured in relation to AX. The error from the history log actually says "the owner of job does not have server access". The job is actually set to run in the context of a user that does have server access, but somehow (a bug maybe) this is ignored by the SQL Server Agent.

The solution is:
  1. Change owner of the job to the SQL Server User that has the necessary permissions to run the report
  2. Grant the SQL Server User permissions to operate on the the Report Server Database (It does needs permissions to inject data, however I gave it full db_owner permissions)
  3. Test the job by doing a manual run
If the job still fails, you can investigate the Job history for any errors. 

Thursday, November 12, 2015

Error during install of Microsoft Report Viewer 2012

When installing the client for AX2012 R3 CU8 or later, one of the requirements is installing Report Viewer 2012. The prerequisite Validation step will inform you the component is missing, and it will provide the download link.

However, depending on your scenario you might need to download and install Microsoft System CLR Types for SQL Server 2012.

The error is "Setup is missing an installation prerequisite".


You can download the necessary components from Microsoft SQL Server 2012 Feature Pack.

Or you can download it directly using the following links:
32bit http://go.microsoft.com/fwlink/?LinkID=239643&clcid=0x409
64b http://go.microsoft.com/fwlink/?LinkID=239644&clcid=0x409