I ran across the following error in SQL Reporting Services 2008 R2 a few weeks ago:

The report execution has expired or cannot be found. [rsExecutionNotFound]

This report contains a very large dataset, so my first inclination was that there was some query tuning and indexing review that needed to be done.  The first thing I did was fire up SQL Server Profiler to verify the query and its parameters.  I kicked off the report and started watching Profiler.  The report cranked away forever, which was the original complaint.  Guess what I saw?  NOTHING!  This report sat there and cranked away and I never saw it query the source DB once.

Now we have a mystery.  I opened the report in BIDS to snoop around some more, and decided to preview it from there.  What did I get?  It ran just fine, although it did take way too long.  At least it actually rendered a report, though.

It’s time for some research. The only things I found on this error were to increase the default report timeout setting and to check the report security context.  I knew it wasn’t the timeout setting, because it rendered the report much faster than that in BIDS.  I could also run all the report queries in SSMS in way less than that amount of time.  I also double checked the security setup and context and nothing was out of whack.

Lucky for me I had another report out there that was almost identical and did not experience this issue.  There were two differences between them.  The first was a date parameter and the second was an ID parameter that was not contained in the second report.  The date parameter was just a default RS parameter and not coming from a query for default values.  The second ID parameter, however, had a query for its values and when the report executed these values were used in an “IN” clause.  It turns out that this parameter was the culprit.  The “IN” clause could not handle the amount of values that were being passed.

I talked with management about the report to review their specific requirements.  It turns out they did not need the option to select IDs, so I removed that parameter and BINGO!  That fixed the issue.

So if you get this error, check your report parameters for anything absurdly large or that might need some tuning.  I should also mention that errors in RS are notorious for not pointing you in the right direction, but there is a reason.  By default full errors are not output to the web to avoid exposing things that should not be publicly available.  You can change that setting, but that is a post for another day.

It drives me nuts not knowing the full reason for an issue or how to track it down faster next time.  Here is another tip to get to the bottom of this faster, that I learned along the way.  I never saw the report hit the DB in Profiler, but if I had run the report from BIDS and watched Profiler I would have seen the root cause of the overloaded “IN” clause right away.  If you have an RS error of any kind on your RS site, I advise you to run it through BIDS and watch Profiler to see the root error from the start.