Actually, the full error in the alert in this System Center Operations Manager 2007 R2 install was:
Failed to store data in the Data Warehouse.Failed to store data in the Data Warehouse. Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
A bit of quick checking and I found that the SQL server instance had the default and incorrect collation of Latin1_General_CI_AS while the OpsMgr databases had the correct collation of SQL_Latin1_General_CP1_CI_AS (check the properties of SQL Server and the databases in SQL Management Studio to verify).
And this pretty much explained why reports from new management packs weren’t appearing in OpsMgr. The odd thing is that this problem went unnoticed for over 6 months and many management packs functioned perfectly well.
I knew what was ahead of me: a SQL rebuild. So that’s what I did, with some guidance from a blog post by Marnix Wolf, MVP. I veered a little from the guidance he gave. I opted to start with a new SQL Reporting DB because it was easier to do this and I had no customisations to rescue. So I didn’t restore it, I didn’t run ResetSRS, and I just needed to reinstall OpsMgr Reporting and supply the details.
Interestingly, the OpsMgr Reporting installed froze about half way through. There were no visible issues, no performance bottlenecks, no clues, nothing to explain the setup hang … except for the Application Log in Event Viewer. There McAfee reported that it was preventing lots of .Net stuff. Uh oh! I temporarily disabled the McAfee protection and the installer wrapped up almost immediately.
Once everything was back I verified that monitoring worked, that the datawarehouse was still OK, and that reports were repopulating and working. But then a flood of alerts came in:
Microsoft.EnterpriseManagement.Common.UnknownServiceException: The service threw an unknown exception. See inner exception for details. —> System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: …
That looked nasty but the fix was easy enough. As Alexy Zhuravlev said, run this on the SQL server against the OperationsManager database:
sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO
After that, everything was okey dokely and the SQL 2008 R2 DB was updated to get it OpsMgr 2012 ready 