There is a bug in Update Rollup 5 which only effects systems which use a System Locale that results in a change to the date and time format. US systems store their date and time in the MM/DD/YY format however here in the UK and many other countries, we store the date as DD/MM/YY. The bug in Update Rollup 5 meant that SCSM isn’t able to understand how a month could have more than 12 days as it isn’t able to use international date formatting.
If you are running System Center 2012 R2 in your environment and you have installed Update Rollup 5 but you are based outside of the USA then this post may well be for you.
Update Rollup 5 is the latest of the regular maintenance updates for Service Manager 2012 R2 and includes a wave of updates but it also comes with a nasty bug up it’s sleeve.
I was working with a customer this week trying to get to the bottom of an issue whereby the Data Warehouse jobs where failing. The MPSyncJob was completing successfully but the next jobs in the Data Warehouse job order, the Extract jobs where failing and reviewing the event log on the Data Warehouse server had an error message “The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value”.
The error message itself isn’t particularly helpful unless you happen to know a bit about SQL and that nvarchar and datetime are both SQL data types for storing row data. I looked back through the logs and found that the jobs started failing the day after we installed an updated version of a custom management pack I had written for them so we uninstalled the MP and I re-ran all of the warehouse jobs which this time succeeded so we knew it was the custom pack at fault.
I reviewed my code in Visual Studio and was happy that everything was as it should be so I turned to the TechNet forums to see what others had to say and sure enough, there where quite a few people on there complaining that after installing Update Rollup 5, they started to see these same Data Warehouse job failures.
It transpires that there is a bug in Update Rollup 5 which only effects systems which use a System Locale that results in a change to the date and time format. US systems store their date and time in the MM/DD/YY format however here in the UK and many other countries, we store the date as DD/MM/YY. The bug in Update Rollup 5 meant that SCSM isn’t able to understand how a month could possibly have more than 12 days as it isn’t able to understand international date formatting with the days and months transposed.
Microsoft have released a hotfix for Service Manager 2012 R2 Update Rollup 5 which updates the Microsoft.EnterpriseManagement.Orchestration.dll file and fixes the issue.
You can obtain the update from http://www.microsoft.com/en-gb/download/details.aspx?id=46368. Once downloaded, apply the update to your Service Manager Management Servers and your Data Warehouse Management Servers. Although not noted as a requirement in the update release notes, I chose to restart the servers just to be certain. After installing the update, Microsoft.EnterpriseManagement.Orchestration.dll will be updated from 7.5.3079.315, the UR5 version, to 7.5.3079.344 to reflect the hotfix installation.
After applying the hotfix, I re-imported the management pack I had written, re-imported the data for the management pack using a CSV Import and I manually triggered the MPSyncJob and the Extract jobs and they all ran without issue and the Data Warehouse is now functional again.
One important note regarding this update is that it states that your Data Warehouse must have completed at least one successful synchronisation before installation. If you are using an existing deployment of SCSM 2012 R2 then this shouldn’t be a problem however if you are working with a new installation then you should pair the Management Group and the Data Warehouse Management Group and complete a sync before you start installing third-party management packs that could trigger the issue. Once the jobs have completed overnight at least once, then install the hotfix and proceed with installing your custom management packs.
One thought on “Nvarchar Data Type Error with SCSM 2012 R2 Update Rollup 5”
Comments are closed.