Scheduled report remain in pending status

Question

I have created SSRS report. Its working fine when I try to view this report on report server.

Now I am trying to use Subscriptions feature of Report Server to export report's in PDF format on schedule basis. For that.

I created New Subscriptions for some of the reports, and I scheduled the reports using shared schedule. The scheduling is working fine.

The issue is with the status of the report subscriptions under subscription tab, it shows

"Failure writing file \viaserver\Shared\Test.pdf: An impersonation error occurred using the security context of the current user"

Answer 1

Have you checked data source setting of the report, and whether the RS execution account has permission to write file at "\viaserver\Shared\"?

Hi Raju

The problem could be a bit complicated as you do not have the log for the report. So just some general suggestions, hope they can help you:

To check whether the whether the report ran successfully within the subscription, you can query executionlog2 in the report server database, by default the query should be like:

select * from ReportServer..ExecutionLog2 where RequestType = 'Subscription'

Instead of generating PDF subscription, can you run the report directly? Or is this report very large? It is because when generating report into pdf format, paginating can consume resource a lot. If you reduce the report size, can subscription run successfully?

The RS subscription relies on SQL Agent, can you find out whether the SQL Agent job ran successfully? To find out the job related to the subscription, you can use below query, where the scheduleID column is what you need to locate the job:

use ReportServer
go

 SELECT S.ScheduleID AS SQLAgent_Job_Name
      , SUB.Description AS Sub_Desc
      , SUB.DeliveryExtension AS Sub_Del_Extension
      , C.Name AS ReportName
      , C.Path AS ReportPath
 FROM ReportSchedule RS JOIN Schedule S ON RS.ScheduleID = S.ScheduleID
      JOIN Subscriptions SUB ON RS.SubscriptionID = SUB.SubscriptionID
      JOIN Catalog C ON RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID
 WHERE C.Name LIKE '%%' 

Answer 2

Here are some additional points to help you debug the issue; I ran into many of these issues today - using SQL 2016 on Server 2012 R2. I'm writing this up mainly for my own reference but in hopes that (a) it will help someone else, and (b) someone can give additional info as to what is happening.

• We have a domain set up, and I was trying to use a domain account as the account to impersonate. Use a local account. The account should have logon rights.

• Set up a share. Only UNC paths are allowed, so be sure that the user set up in the previous bullet point has (a) NTFS folder permissions and (b) share permissions. These are 2 separate things. Do this even if the share is on the local machine.

• I like to stop reporting services, rename the log file in <drive>\Program Files\Microsoft SQL Server\MSRS(SSRS version).MSSQLSERVER\Reporting Services\LogFiles\, then start it again so there's a fresh file and you're not scrolling down to the bottom of a 5MB file.

• Sacrifice something - an animal, your first born - to the SQL Server Reporting Services gods. We're still trying to determine what happened, but at this stage we get the PDFs to generate to a local folder but it still writes out each one having an error, same errors that we were getting when it wasn't writing out the PDFs. It is writing out the PDF files. I do not know why it also writes out the same error as it was when it was failing to write out each PDF, I am thoroughly confused, and so is SSRS, apparently. I'll be sure to update this with additional info as we figure this out, but it's definitely not the easiest MS product we've had the pleasure of dealing with.