Error ‘Attempted to perform an operation that was forbidden by the CLR host.’

When trying to run a Service Level Tracking report in OpsMgr R2, one might bump into this error: ‘An error has occured during report processing. The value expression for the query parameter '@StartDate' contains an error: Attempted to perform an operation that was forbidden by the CLR host.’. All other reports work just fine.

It took a long while to get to the root cause here. First I thought CLR wasn’t on. CLR is needed in order for OpsMgr to do it’s work. I already blogged about this, found here. But when I checked, all seemed well. And as Graham Davies stated, since it generates an error, it is working.

Then I thought it might be a bug since the servers are W2K08 SP2 based and the SQL server is SQL 2K08 SP1. Perhaps there is something amiss? So I built a new test environment yesterday evening late, based on these components. But here all was just fine. So it was an isolated problem.

Time to focus on the first part of the error message: ‘The value expression for the query parameter '@StartDate' contains an error’. Could it be that the used formats for the dates didn’t match with the SRS instance?

So I checked the Collation of the SRS instance and compared it with the one of the Database engine. Indeed, these were different but the same difference was to be found in the newly built test environment as well and there all is OK.

Still, I have had strange issues with the Regional and Language options before so I still was looking in that direction. Time to start SQL Profiler and run the problematic report in order to see whether something pops up. And indeed, an error popped up and it seemed to be the date format. It showed different formats like month/day/year and year-month-day:

When checking the Regional and Language options on the server hosting the SRS instance, indeed it showed multiple language settings under the tab ‘Keyboards and Languages’. First the selection US was active and later on some one had changed it to Dutch. No problems with the Dutch setting but when SRS is installed with the english setting and it get’s it data delivered in another format (dutch in this case), it goes wrong.

Since OpsMgr was freshly installed and everything wasn’t in production yet, I decided to rectify it for good by doing these actions:

1. Removing the US setting in the tab ‘Keyboards and Languages’ of the Regional and Language options and defaulting to Dutch on the SRS server. Checking the (Root) Management Servers and these were OK.
   
   
2. Removing OpsMgr Reporting Server
   
   
3. Deleting the OpsMgr DW database (it was only in place for two days, so no harm there)
   
   
4. Deleting the SRS related databases and website
   
   
5. Recreate the SRS databases and website
   
   
6. Running the tool ResetSRS.exe on the SRS instance
   
   
7. Checking the url http://localhost/reports in order to see it is working
   
   
8. Reinstalled OpsMgr Reporting
   
   
9. Waited for an hour to get all the reports back. And checked the problematic report:
   

BINGO!!! :)

Lesson learned: ALWAYS DOUBLE CHECK THE REGIONAL SETTINGS ON ANY SERVER HOSTING ANY OPSMGR ROLE BEFORE INSTALLING ANYTHING…