When trying to update my Microsoft Dynamics CRM 4.0 development server to Rollup 10 I got this error:
Action Microsoft.Crm.Setup.Common.Update.DBUpdateAction failed.
The INSERT statement conflicted with the FOREIGN KEY constraint "lk_timezonerule_timezonedefinitionid". The conflict occurred in database "Ros_MSCRM", table "dbo.TimeZoneDefinitionBase", column 'TimeZoneDefinitionId'.
The INSERT statement conflicted with the FOREIGN KEY constraint "lk_timezonerule_timezonedefinitionid". The conflict occurred in database "Ros_MSCRM", table "dbo.TimeZoneDefinitionBase", column 'TimeZoneDefinitionId'.
The statement has been terminated.
The statement has been terminated.
After some exploring I realized that content of TimeZoneDefinitionBase table was not the same across all organizations on this server. Two organization had some old version, that had wrong data in UserInterfaceName and StandardName and less rows that other organizations (89 instead of 97, so 8 less).
I fixed the data on database level and Rollup 10 installed with not problems.
PLEASE NOTE: This MIGHT be related to my recent failed update to Rollup 9, so I’m not saying it will happen to you or that Rollup 10 is broken!
Here are scripts that helped fixed two of my organizations. Note that you should substitute GOOD_MSCRM and BAD_MSCRM with your database names where GOOD_MSCRM is database with correct values in TimeZoneDefinition and BAD_MSCRM is database with broken data.
WARNING: Use at you own risk! This fix is probaby not supported by Microsoft!
-- insert missing rows
insert into
BAD_MSCRM..TimeZoneDefinition
(ModifiedOn, TimeZoneCode, OrganizationId, TimeZoneDefinitionId, CreatedOn,
Bias, DaylightName, CreatedBy, UserInterfaceName, StandardName, RetiredOrder,
ModifiedBy, DeletionStateCode)
select
ModifiedOn, TimeZoneCode, OrganizationId, TimeZoneDefinitionId, CreatedOn,
Bias, DaylightName, CreatedBy, UserInterfaceName, StandardName, RetiredOrder,
ModifiedBy, DeletionStateCode
from (
select
g.*
from
GOOD_MSCRM..TimeZoneDefinition g
left join
BAD_MSCRM..TimeZoneDefinition b on g.TimeZoneDefinitionId = b.TimeZoneDefinitionId
where
b.TimeZoneDefinitionId is null
) x
-- update existing rows
update
BAD_MSCRM..TimeZoneDefinition
set
UserInterfaceName = tz.UserInterfaceName,
StandardName = tz.StandardName
from
GOOD_MSCRM..TimeZoneDefinition tz
where
BAD_MSCRM..TimeZoneDefinition.TimeZoneDefinitionId = tz.TimeZoneDefinitionId and
(
BAD_MSCRM..TimeZoneDefinition.UserInterfaceName <> tz.UserInterfaceName or
BAD_MSCRM..TimeZoneDefinition.StandardName <> tz.StandardName
)