Recently on our EMEA CRM Support Team, we received a few calls related with issues during the latest update rollups (URs) installation on a multiple organization environments.

For example, we had some cases where we were unable to install the UR10 and we were receiving this error during the UR installation:

The INSERT statement conflicted with the FOREIGN KEY constraint "lk_timezonerule_timezonedefinitionid". The conflict occurred in database "xpto_MSCRM", table "dbo.TimeZoneDefinitionBase", column 'TimeZoneDefinitionId'.
 

And the source of this issue, was a query like this one

INSERT INTO TimeZoneRuleBase (ModifiedOn, CreatedOn, DeletionStateCode, TimeZoneRuleVersionNumber, EffectiveDateTime, TimeZoneDefinitionId, TimeZoneRuleId, Bias, StandardBias, StandardYear, StandardMonth, StandardDay, StandardDayOfWeek, StandardHour, StandardMinute, StandardSecond, DaylightBias, DaylightYear, DaylightMonth, DaylightDay, DaylightDayOfWeek, DaylightHour, DaylightMinute, DaylightSecond) VALUES (getutcdate(), getutcdate(), 0, 4, '2009/1/1', 'A57C8407-74AF-47f0-B1D8-86575C134EC0', 'de8f561d-a33b-4c1c-944d-2944c05876f6', 0, 0, 0, 8, 3, 4, 23, 59, 59, -60, 0, 5, 5, 0, 23, 59, 59)

These queries are included in the update rollup patches, in order to update the CRM time zones and they could be executed during the UR installation.

Basically, when we install one of the latest URs (for example UR10), during its installation, it will checks the current revision number for each organization database present in the CRM installation and it applies the database patches released since that point (current revision number) until UR10.

However in some cases, we can easily end with many organization databases in a upper revision number than the real database patching level (revision number).

Behind this situation are two known issues:

  • Database updates are not applied when we create or import an organization.
  • When an update rollup is uninstalled, the revision number of all organizations is updated to the previous installed UR.

So, if we imagine the following environment with three organizations:

  • One organization (org1) created during the installation and where we have applied UR7
  • The second organization (org2) was imported from another installation updated with UR5
  • And the third (org3) was created recently

Based on the above known issues, this should be their current revision number:

  • ORG1 is in revision 2138 (UR7)
  • ORG2 is in revision 1644 (UR5)
  • ORG3 is in revision 3 (RTM)

However, if we install UR9 and then we’ve to install it for some reason, we will be facing the second known issue:

  • We will end with all organizations in the revision number of the previous installed UR (UR7 in this scenario).

This means that all the three organizations will be in the revision 2138 (UR7), even when some of them (ORG2 and ORG3) have not installed all DBupdates released until revision 2138 (UR7).

For example, they will not have the SubscriptionManuallyTrackedObject table (table introduced with UR7) and the number of lines in the TimeZoneDefinitionBase table will be different between the organizations.

The SubscriptionManuallyTrackedObject table will cause synchronization issues, on outlook clients with UR7 installed and we will experience an error like this one installing future URs:

The INSERT statement conflicted with the FOREIGN KEY constraint "lk_timezonerule_timezonedefinitionid". The conflict occurred in database "ORG3_MSCRM", table "dbo.TimeZoneDefinitionBase", column 'TimeZoneDefinitionId'.
 

Because, we are trying to insert a new line in the TimeZoneDefinitionBase table, referring a TimeZoneDefinitionId that is not yet presented in this CRM organization database (because it was supposed to be created by a DB patch not applied to this database).

 

RESOLUTION:

In order to solve this issue, it’s import that we confirm what the current status of our organizations are.

We could know it, by simple query like “SELECT Revision from BuildVersion” against each organization database to know the revision number, but if we have 100 or more organizations, this could be not that simple.

To help with this, you can use the following SQL script, to check the presence of the SubscriptionManuallyTrackedObject table; the number of lines in the TimeZoneDefinitionBase table and the Revision number of each organization database:

CREATE PROCEDURE ms_FindTableNameInAllDatabase

@TableName VARCHAR(256)

AS

DECLARE @DBName VARCHAR(256)

DECLARE @varSQL VARCHAR(512)

DECLARE @getDBName CURSOR

SET @getDBName = CURSOR FOR

SELECT name

FROM sys.databases

WHERE name LIKE '%_MSCRM%'

CREATE TABLE #TmpTable (Org_Name VARCHAR(256),

SubscriptionManuallyTrackedObject_is_present VARCHAR(256),

Lines_in_TimeZoneDefinitionBase VARCHAR(256),

DB_Revision VARCHAR(256))

OPEN @getDBName

FETCH NEXT

FROM @getDBName INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @varSQL = 'USE ' + @DBName + ';

DECLARE @check VARCHAR(256), @lines VARCHAR(256), @rev VARCHAR(256);

SELECT @check = COUNT(*) FROM sys.tables WHERE name LIKE ''%' + @TableName + '%'';

SELECT @lines = COUNT(*) FROM TimeZoneDefinitionBase

SELECT @rev = Revision from BuildVersion;

INSERT INTO #TmpTable VALUES ('''+ @DBName +''',

@check,

@lines,

@rev);’

EXEC (@varSQL)

FETCH NEXT

FROM @getDBName INTO @DBName

END

CLOSE @getDBName

DEALLOCATE @getDBName

SELECT *

FROM #TmpTable

DROP TABLE #TmpTable

GO

EXEC ms_FindTableNameInAllDatabase 'SubscriptionManuallyTrackedObject'

GO

DROP PROCEDURE ms_FindTableNameInAllDatabase

 

Confirmed the scenario, we’ve two options:

  • We can uninstall all URs until RTM (this will update the revision number on all organizations to 3) and then install the latest UR

OR

  • We can update the revision number of all organization to 3 (RTM) and then install the latest UR

 

Forcing this way, the next UR installation to apply all DBpatches released between RTM and the UR revision, on all organizations.

To updated the Revision number on all organizations to 3 (RTM), you can use the same SQL script, with this little modification:

CREATE PROCEDURE ms_FindTableNameInAllDatabase

@TableName VARCHAR(256)

AS

DECLARE @DBName VARCHAR(256)

DECLARE @varSQL VARCHAR(512)

DECLARE @getDBName CURSOR

SET @getDBName = CURSOR FOR

SELECT name

FROM sys.databases

WHERE name LIKE '%_MSCRM%'

CREATE TABLE #TmpTable (Org_Name VARCHAR(256),

SubscriptionManuallyTrackedObject_is_present VARCHAR(256),

Lines_in_TimeZoneDefinitionBase VARCHAR(256),

DB_Revision VARCHAR(256))

OPEN @getDBName

FETCH NEXT

FROM @getDBName INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @varSQL = 'USE ' + @DBName + ';

DECLARE @check VARCHAR(256), @lines VARCHAR(256), @rev VARCHAR(256);

SELECT @check = COUNT(*) FROM sys.tables WHERE name LIKE ''%' + @TableName + '%'';

SELECT @lines = COUNT(*) FROM TimeZoneDefinitionBase

SELECT @rev = Revision from BuildVersion;

INSERT INTO #TmpTable VALUES ('''+ @DBName +''',

@check,

@lines,

@rev);

update BuildVersion set Revision = ''3'''

EXEC (@varSQL)

FETCH NEXT

FROM @getDBName INTO @DBName

END

CLOSE @getDBName

DEALLOCATE @getDBName

SELECT *

FROM #TmpTable

DROP TABLE #TmpTable

GO

EXEC ms_FindTableNameInAllDatabase 'SubscriptionManuallyTrackedObject'

GO

DROP PROCEDURE ms_FindTableNameInAllDatabase

Since you are in UR11, both DBpatches known issues are now corrected and every time you create or import an organization, all DBpatches until the most recent UR installed, will be applied to that organization.

It’s also important to refer the following by design behaviors:

  • Organizations not present in Deployment Manager (organizations that were deleted), are not updated by URs.
  • Organizations that are disabled or in a pending state, are not updated by URs.

CRM revision numbers:

RTM: 4.0.7333.3

Rollup 1  - 4.0.7333.1113

Rollup 2  - 4.0.7333.1316

Rollup 3  - 4.0.7333.1408

Rollup 4  - 4.0.7333.1551

Rollup 5  - 4.0.7333.1644

Rollup 6  - 4.0.7333.1750

Rollup 7  - 4.0.7333.2138

Rollup 8  - 4.0.7333.2542

Rollup 9  - 4.0.7333.2644

Rollup 10 - 4.0.7333.2741

Rollup 11 - 4.0.7333.2862

Related KB articles:

The database of a new organization does not contain the metadata hotfixes after you apply the metadata hotfixes to the new organization in Microsoft Dynamics CRM 4.0

http://support.microsoft.com/kb/980627

 

Update Rollup 11

http://support.microsoft.com/kb/981328

 

Greetings,

José Alves