I am investigating a SSRS 2012 Report which used to return data from an Oracle database. However, recently it stopped returning data. The report will sit there and run forever until something times out. Our report catalog database is also on a separate server which does not show any issues in the logs or while monitoring the performance. I do not see any errors in the Event log, SQL Server Logs, Reporting Services logs or the Oracle database logs. If I strip out the query from the report it returns rows in a few minutes. Is there a method for monitoring what is happening with the communication between the report server and the oracle server? How can I troubleshoot this issue if none of the logs are reporting any problems?
Looks like I am now getting Timeout errors in the report logs.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
When I run the query against the Oracle database using SQL Plus, it takes about 8 minutes to run.
Best Answer
I would recommend the following:
1) Run the report and check Oracle's session (v$session) view to see if the Oracle database even registered the connection and the query was passed to it.
a) if YES, then check your report server's timeout settings. If the initial data return is elapsing the timeout period in either your report or your RS config file, this may likely be the reason for your timeout issue. You can check out report timeout periods in your data source configurations. You can check time out RS settings in your report server's config file.
b) if NO, you've got something else going that is more fundamental in connecting to the Oracle db and submitting the query.