Reporting services error trying to access a object that isn't called in the report

0
votes

have a strange one. We are running Sql Server 11.0.3000. The report is giving an error

An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'Audit_Detail'. (rsErrorExecutingCommand) Invalid object name 'dbAudit.tblRangeReport'.

The thing is, the report uses 3 stored procs to go after data in a database called dbSalarySurvey. The three sp's do not mention the dbAudit.tblRangeReport. There are no functions or views in the 3 sp's that access that table. Further more, if i run the report in VS 2012 the report runs. If i run profiler on the dbAudit table when VS runs the report, the report does not query the dbAudit table. Only when running the report from the web browser do i get this error. This more detailed version of the error come from running the report on the Sql Server REporting services computer.

I have tried restarting the reporting services service. We have also deleted this one report from the reporting services server and republished it. Have also tried to republish the dataset. Other reports in this project that use the datasource and the same tables run.

Anyway, was wondering if anyone had any sugguestions. Thanks shannon

It's fixed. it was a pebkak error in the stored proc. It's really strange though. Honest to goodness, the proc was running in my dev env but not when running from a browser. I had already checked profiler like was suggested below and done the xml scan too. In the end.. just missed it i guess.

1
reporting-services
what does the dataset Audit_Detail use? – KrazzyNefarious
Audit_Detail uses a stored proc to get it's data. The sp runs fine in Studio Manager from the dbSalarySurvey database. The dbAudit.tblRangeReport is not used in the stored proc.. not in the text or a function that is also being called in the sp. thanks – jvcoach23
@jvcoach23 - Can you search for tblRangeReport in the report XML? – Sébastien Sevrin

1 Answers

0
votes

You need to trace the SQL to determine.

  1. Are you connecting to the right database?
  2. Are the procs you expecting to see, the ones actually being run?
  3. The procs you are running are correct?

Open SQL Server Profiler

  • Start New Trace
  • Click Event Selection, Select the following events enter image description here
  • Click - Show All columns
  • Go into Column Filters
  • and filter on your DB, and possibly even your login name (the login name of the SSRS server) enter image description here

Once you've done that, call your report and look to make sure all the procs are called that you expect. Track issues from there.