I get an error message " An error has occurred during report processing. Query execution failed for dataset [sprocs name]" when running SSRS report.
The stored procedures for this report can be executed without error in the SQL server. It has a CTE query that uses a linked server to retrieve data from a different database server.
There is no error in the windows log that points to this issue.
My guess is that it has to do with the linked server dropping connection but I can't seem to prove that is the case because I can't find any error that points to it and also sprocs rans fine. And also opening the report frequently with the error above from SSRS, to check if it is working, brought down the report server.
Can anyone help troubleshoot this issue?