1

I have several SSRS reports that fail (immediately) with the following unhelpful messaging.

An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'Widgets'. (rsErrorExecutingCommand) A severe error occurred on the current command. The results, if any, should be discarded.

The report does not show up in the execution log table in ReportServer so evidently it's not even getting that far. Inside of the Widgets dataset, a stored procedure is called from another database.

I should mention the (possible) crux of the issue, which is that both the stored procedure and the tables referenced in the dataset are synonyms pointed to those objects in a database on a linked server.

I have confirmed that the synonyms are correct and work, but if I change the datasource for the report to point directly to the DB where the objects actually live, then the report works fine. It's only when I point it to the one with the synonyms that it fails. But I can't see any reason why it shouldn't work via the synonyms. The purpose of the db with the synonyms is a 'stepping stone' if you will, for a database migration cutover process and will really simplify the process if I can get these reports working in the meantime using the synonyms. It is SSRS/SQL 2008R2 hence the need to migrate.

The thing I find odd is this: if I run any query (or the actual stored procedures called by the reports) from SSMS against both the database containing the synonyms, and the database containing the actual tables and stored procedures, I get identical results back showing that the tables and stored procedures do work via the synonyms. This is using the same DB user specified in the SSRS data sources.

I'm frustrated with the lack of info in the "severe error occurred" messaging and haven't been able to find any other indication of what might be happening. I see the same error verbiage in the Reporting Services logs.

I have tried temporarily granting sysadmin role to the user on both DBs without any change in the results. It feels like a perms issue but haven't figured out a way to demonstrate that.

I have also tried misspelling the db name and/or the stored procedure name in the rdl to confirm that it is actually able to hit them. In both cases I get the error that the stored procedure wasn't found, which confirms the report is able to find them at least, since I dont get those errors when it is entered correctly.

jarlh
  • 42,561
  • 8
  • 45
  • 63
OKreboots
  • 11
  • 3
  • Does [this information](https://stackoverflow.com/a/10848459/7644018) help? – Paul T. Aug 17 '23 at 01:51
  • 1. Try running Profiler to see what is being executed and how far it gets. 2. Check the drivers you are using in SSRS. (I did encounter driver problems with instead of triggers a long time ago.) Test using the same driver in SSMS. 3. This may help: https://stackoverflow.com/questions/35296182/cannot-execute-synonym-stored-procedure-with-sql-server-odbc-driver-works-with – Alex Aug 17 '23 at 21:56
  • 1
    I did run profiler to see what is being executed, but wasn't able to make sense of 'how far it gets' per se, but that was where I got the queries and exec commands to try running directly via SSMS. They all worked fine when not executed via the report. – OKreboots Aug 18 '23 at 14:44
  • 1
    I went through the thread posted by Paul T but found most of those cases, the queries etc would not have worked in SSMS as they have for me, because their root issue was demonstrable outside of SSRS – OKreboots Aug 18 '23 at 14:52
  • 1
    I did find some additional leads from your thread though Alex, that did look to deal with failures when using synonyms but I have hit a point where this was supposed to simplify our migration cutover, and have to recognize that because of this snag, it is now looking to be at least as troublesome as updating all the reports and subscriptions. I wanted to avoid that due to the tedious nature of dealing with hundreds of reports... but, it is an approach I know works so I'm shifting gears now. Thanks for the replies – OKreboots Aug 18 '23 at 14:53
  • Next time use "@Nick" command e.g. @OKreboots when mentioning names. This sends me an alert that there is a message for me. – Alex Aug 22 '23 at 09:30

0 Answers0