CRM DBupdates issue, installing an update rollup on a multiple organization environment
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
https://support.microsoft.com/kb/980627
Update Rollup 11
Greetings,
José Alves