{"id":12541,"date":"2012-05-16T08:03:00","date_gmt":"2012-05-16T07:03:00","guid":{"rendered":"https:\/\/aidanfinn.com\/?p=12541"},"modified":"2012-05-16T08:03:00","modified_gmt":"2012-05-16T07:03:00","slug":"yesterdays-fun-in-opsmgr-failed-to-store-data-in-the-data-warehouse","status":"publish","type":"post","link":"https:\/\/aidanfinn.com\/?p=12541","title":{"rendered":"Yesterday\u2019s Fun In OpsMgr: Failed to store data in the Data Warehouse"},"content":{"rendered":"

Actually, the full error in the alert in this System Center Operations Manager 2007 R2 install was:<\/p>\n

\n

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.<\/p>\n<\/blockquote>\n

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).<\/p>\n

And this pretty much explained why reports from new management packs weren\u2019t appearing in OpsMgr.  The odd thing is that this problem went unnoticed for over 6 months and many management packs functioned perfectly well.<\/p>\n

I knew what was ahead of me: a SQL rebuild.  So that\u2019s what I did, with some guidance from a blog post<\/a> 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\u2019t restore it, I didn\u2019t run ResetSRS, and I just needed to reinstall OpsMgr Reporting and supply the details.<\/p>\n

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 \u2026 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.<\/p>\n

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:<\/p>\n

\n

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: \u2026<\/p>\n<\/blockquote>\n

That looked nasty but the fix was easy enough<\/a>.  As Alexy Zhuravlev<\/a> said, run this on the SQL server against the OperationsManager database:<\/p>\n

sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO<\/p>\n

After that, everything was okey dokely and the SQL 2008 R2 DB was updated to get it OpsMgr 2012 ready<\/a> \"Smile\"<\/p>\n

Technorati Tags: OpsMgr<\/a>,Operations Manager<\/a>,SQL<\/a>,System Center<\/a><\/div>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading “Yesterday\u2019s Fun In OpsMgr: Failed to store data in the Data Warehouse”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[30],"tags":[83,187,192,193],"class_list":["post-12541","post","type-post","status-publish","format-standard","hentry","category-opsmgr","tag-operations-manager","tag-opsmgr","tag-sql","tag-system-center"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t