Crm 2011 Roll Up 4 and Auditing Issues

One of our clients has been experiencing an “Unexpected Error has occurred” error message when viewing auditing records. The error seemed fairly sporadic and would only occur for some entity records. In the trace logs a date format exception could be identified corresponding to the time when an audit record was being viewed.

This issue was raised with Microsoft and a fix was provided in Roll Up 4. The update changes the way date fields are written to the audit table, but nothing is done to the existing records and they will still cause the error. This caused me to re-raise the error with Microsoft; the result of this was that a script would have to be used to repair the data. It took a couple of iterations with MS to get a script which did the job.

Here is what I used to get the job done, note you only want to update the audit records which are in the system prior the Roll Up 4 being applied.

begin transaction

SET DATEFORMAT dmy
GO

declare @rollupDate datetime
set @rollupDate = '4/10/2011 6:35:32 am'


update Audit
set ChangeData = CONVERT(char(11), CAST(REPLACE(ChangeData, ' a.m.', 'AM') as date), 101) + CONVERT(char(11), CAST(REPLACE(ChangeData, ' a.m.', 'AM') as time), 108)
where ChangeData like '%/%/%:%:% a.m.' and CreatedOn < @rollupDate and LEN(ChangeData) < 25


update Audit
set ChangeData = CONVERT(char(11), CAST(REPLACE(ChangeData, ' p.m.', 'PM') as date), 101) + CONVERT(char(11), CAST(REPLACE(ChangeData, ' p.m.', 'PM') as time), 108)
where ChangeData like '%/%/%:%:% p.m.' and CreatedOn < @rollupDate and LEN(ChangeData) < 25


select objectid,changedata from AuditBase
where ChangeData like '%/%/%:%:%' and CreatedOn < @rollupDate and LEN(ChangeData) < 25


commit