SSRS report doesn't always get executed

Question

We are using SQL Server 20016 SP2 Standard Edition.

I have a report that I created in Visual Studios. There are 5 parameters in the report. When I run the report in VS, it always works.

I have deployed the report to the reporting server. When I run the report from the reporting server, it doesn't always seem to execute. When I click the View Report button, I get the spinning wheel and loading, but it just keeps spinning. I have tried running SQL against the database server that the report is using to see if the SQL is running, but it doesn't show up. Also, I try to look at the execution logs and there isn't an entry for the execution.

Now, the report sometimes runs. After clicking the View Report button, I can see the SQL being run on the database server and an entry is entered in the execution log. I just can't figure out why it sometimes runs and why it sometimes doesn't.

Answer 1

Hi @Daniel Luchinski ,

Are you using stored procedure for the report?

Have you checked the error log to see if there would be some message.

Log location should be:

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles

Do you mean the report keep loading and no data shows and never time out?

Regards，
Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

What can I do if my transaction log is full?--- Hot issues November

How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

Hi @ZoeHui-MSFT ,

I am not using a stored procedure in the report.

I have looked in the log files. I see the following...

When I go into the report, I see the following 3 lines. There are two parameters that pull values from the database...
library!ReportServer_0-41!2098!11/16/2020-08:49:32:: i INFO: Call to GetItemTypeAction(/AnTox Reports/Case Reports/TherapyDetails). User: ASPCA\danl.
library!ReportServer_0-41!1f90!11/16/2020-08:49:33:: i INFO: Entering StreamRequestHandler.ExecuteCommand - Command = StyleSheet
library!ReportServer_0-41!1f90!11/16/2020-08:49:33:: i INFO: Exiting StreamRequestHandler.ExecuteCommand - Command = StyleSheet (success)

Once I fill in all my parameters and hit View Report, the following line is added...
library!ReportServer_0-41!620!11/16/2020-08:52:49:: i INFO: Call to GetItemTypeAction(/AnTox Reports/Case Reports/TherapyDetails). User: ASPCA\danl.

The SSRS report says Loading and the wheel just spins.

When the report does work (again, it doesn't always fail), I get these lines in the log...
library!ReportServer_0-41!1f90!11/16/2020-09:00:03:: i INFO: Call to GetItemTypeAction(/AnTox Reports/Case Reports/TherapyDetails). User: ASPCA\danl.
library!ReportServer_0-41!1c80!11/16/2020-09:00:04:: i INFO: Entering StreamRequestHandler.ExecuteCommand - Command = StyleSheet
library!ReportServer_0-41!1c80!11/16/2020-09:00:04:: i INFO: Exiting StreamRequestHandler.ExecuteCommand - Command = StyleSheet (success)
library!ReportServer_0-41!308!11/16/2020-09:00:44:: i INFO: Call to GetItemTypeAction(/AnTox Reports/Case Reports/TherapyDetails). User: ASPCA\danl.
library!ReportServer_0-41!308!11/16/2020-09:00:47:: i INFO: Call to GetItemTypeAction(/AnTox Reports/Case Reports/TherapyDetails). User: ASPCA\danl.
library!ReportServer_0-41!308!11/16/2020-09:00:47:: i INFO: RenderForNewSession('/AnTox Reports/Case Reports/TherapyDetails')

On the report server, I run the following command to see what reports have been executed...

USE ReportServer;  
GO  
SELECT el2.username,   
el2.InstanceName,   
el2.ReportPath,   
el2.TimeStart,   
el2.TimeEnd,   
el2.[Status],  
isnull(el2.Parameters, 'N/A') as Parameters   
FROM ExecutionLog2 el2  
order by el2.TimeStart desc  
GO  

If the report actually works and returns data, I see a row in the output. However, if it is just where Loading is just spinning, I don't see a row in the results.

As for never timing out, the default is set to 30 minutes. I thought I let one go longer than that and didn't see it time out. Since I don't really want to wait 30 minutes, I changed the time out to 5 minutes. I ran the report a number of times (because it worked the first 4 or 5 times) and finally got one that wasn't working. After 5 minutes, it still said Loading with the wheel spinning. I had a meeting, so I let it just run. It has been almost 40 minutes and still hasn't canceled. I thought maybe the report timeout of 5 minutes may not have worked, but the system timeout of 30 minutes didn't work either.

Also, when I kick off the report, I go to SSMS and keep running the following query on the database that the report hits. Now, there is a small chance that I could miss it. However, the report should take at least 10-20 seconds and I am not seeing it pop into the results...

SELECT sqltext.TEXT,  
req.session_id,  
req.status,  
req.command,  
req.cpu_time,  
req.total_elapsed_time  
FROM sys.dm_exec_requests req  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext