Bite my bytes

What I learn by day I blog at night - A blog from Microsoft Consultant working from Ljubljana, Slovenia

  Home :: Contact :: Syndication  
  999 Posts :: 7691 Comments :: 235 Trackbacks

Search

Most popular posts

Categories

My Projects

Archives

Stuff


Copyright © by David Vidmar
 
Contact me!
 
LinkedIn Profile
 
 
 

image

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
    )

 

Posted on Monday, April 12, 2010 9:14 AM | Filed under: MS Dynamics CRM |

Feedback

# re: Updating Dynamics CRM 4.0 with Rollup 10 failed (but it’s probaby my fault) 4/20/2010 7:08 PM Fagor
Very helpful piece of code thanks!

# re: Updating Dynamics CRM 4.0 with Rollup 10 failed (but it’s probaby my fault) 4/27/2010 10:17 AM UR hurry
Thanks a lot Vidmar - this helped also with UR8 update (cannot update yet to UR9 or 10 due to integrations)

# re: Updating Dynamics CRM 4.0 with Rollup 10 failed (but it’s probaby my fault) 5/13/2010 8:31 AM add site free
that was good

# re: Updating Dynamics CRM 4.0 with Rollup 10 failed (but it’s probaby my fault) 6/8/2010 1:33 PM Les Capel-Moodley
Thanks a lot Vidmar for shortening what was destined to be a very long night of updates. Did you manage to find out what had modified the TimeZoneDefinition table?

# re: Updating Dynamics CRM 4.0 with Rollup 10 failed (but it’s probaby my fault) 6/9/2010 10:48 AM David
No, I never found out what the problem was. But then again, I never really tried. It was just on my dev organization.

# re: Updating Dynamics CRM 4.0 with Rollup 10 failed (but it’s probaby my fault) 8/13/2010 10:54 PM Ron Lohoff
We were doing our update to rollup 11 expecting no problems but ran into this exact issue with the TimeZoneDefinitionBase. this post saved me a lot of time so I'm very appreciative.

Comments have been closed on this topic.