So, as you may know, SSRS cannot host up reports for multiple instances of TFS, or for other applications period. IOW, TFS SSRS instances MUST be dedicated. The reason is pretty obvious once you dig around in the properties of the reporting databases on your report server, but trust me on this unless you really WANT to know how it all works under the covers.
Long story short, we accidentally configured two different TFS application tiers (TFS1 running TFS2012 and TFS2 running TFS2010) to use the same instance of SSRS, doh! We only need reporting on TFS1, for the record. After some troubleshooting we found that the connection strings for the TFS2010OlapReportDS and TFS2010ReportDS databases on the report server pointed to TFS2 and not the original one any more (TFS1). But oddly, the reports on the new TFS instance don't work either, I would have assumed that ONE of the instances would have had reporting that worked. I went to the Reports folder, and could see all of the reports for all of the team projects across the both TFS1 and TFS2 but always received this error, on every single report:
- An error has occurred during report processing. (rsProcessingAborted)
- Query execution failed for dataset 'dsIteration'. (rsErrorExecutingCommand)
- For more information about this error navigate to the report server on the local server machine, or enable remote errors
Anyway, I digress.
I figured a good first step was turning off Reporting on TFS2, and then reconfiguring SSRS for TFS1 in an effort to "reset" the connection. But I could turn off reporting on TFS2. I assumed that normally I *should* be able to do this, just un-check the "Use Reporting" feature and it's gone right? Maybe there is something amiss with the TFS 2010 instance? It is brand new, so not sure how it could already be corrupted. Here is the error I receive when I try to "turn off" reporting on TFS1:
I did a lot of searching of MSDN and forums and couldn’t find anything that seemed to help. I got desperate and tried a different order of operations, a "Hail Mary" if you will, and it worked!
I could not turn off reporting on TFS2 for some reason, but it occurred to me that the error message I was getting ("the database is not properly configured") was rather generic and could mean a LOT of things. And alas I do not have remote login access to the SSRS instance (don't get me started on the why or what of that!), so I couldn't even do research on it. So instead I focused on getting TFS2 WORKING with SSRS even through the end goal was turning off reporting. I went to the TFS1 app tier that had been connected to SSRS successfully originally, went into the Admin Console and unchecked "Use Reporting" to break the connection. That worked great, of course.
Next I went back to TFS2, and via the Admin Console verified the SSRS configuration information to hit the report server was all correct (it was), re-started all the jobs, and rebuilt the warehouse. Once reporting was working again on TFS2, I tried to turn it off again, and this time when I unchecked "Use Reporting" it was successful. So apparently if reporting is broken, you cannot turn it off. Great.
Anyway, next I went back to TFS1, reconfigured reporting through the admin console and now all is well with the world again. Oy, I need a drink.