Import Organization error: The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessUnitExtensionBase_BusinessUnitBase".

Hi,

After making a DB backup of an existent organization, restoring it with a different name and attempting to Import the Organization (a ‘usual task’ in CRM), we came across the following error when import was about to finish:

 

Error

The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessUnitExtensionBase_BusinessUnitBase". The conflict occurred in database "<DBNAME>", table "dbo.BusinessUnitExtensionBase", column 'BusinessUnitId'.

image_thumb[1]

Organization ended up in disabled state or just completely fade out from Deployment Manager.

After some research, we just found only one thread in MSDN forums that talks about it, which was pretty odd BTW.

 

Cause

It all seems to be a bug in the import process, when attempting to import and organization with same OrganizationId than an existent one and (probably) because additionally, the Business Unit entity has been customized (in our case, just added 3 fields). That may explain why only one thread talks about it.

 

Solution

The solution (at least what worked for us) was to apply the following SQL script from Frankie Smart in the new organization DB which we want to import:

DECLARE @OldOrganizationId uniqueidentifier, @NewOrganizationId uniqueidentifier
 
 -- The Old OrganizationId
 SET @OldOrganizationId = (SELECT TOP(1) OrganizationId FROM OrganizationBase)
 
 -- The New OrganizationId
 SET @NewOrganizationId = (SELECT NEWID())
 
 --PRINT @OldOrganizationId
 --PRINT @NewOrganizationId
 
 -- Table with all Found Columns with the OrganizationId
 DECLARE @FoundOrganizationIds TABLE (Id bigint identity(1,1), TableName nvarchar(max), ColumnName nvarchar(max), ColumnValue nvarchar(max))
 
 -- Table with all uniqueidentifier Columns in the Database
 DECLARE  @FoundUniqueIdentifierColumns TABLE(Id bigint identity(1,1), TableName nvarchar(max), ColumnName nvarchar(max))
 
 -- Search for all uniqueidentifier Columns in the Database 
 INSERT INTO @FoundUniqueIdentifierColumns
 SELECT
   col.TABLE_NAME, col.COLUMN_NAME 
 FROM 
  INFORMATION_SCHEMA.TABLES tbl INNER JOIN 
  INFORMATION_SCHEMA.COLUMNS col ON tbl.TABLE_NAME = col.TABLE_NAME
 WHERE 
  tbl.TABLE_TYPE = 'BASE TABLE' AND 
  col.DATA_TYPE IN ('uniqueidentifier')
 
 DECLARE @ColumnCount bigint
 SET @ColumnCount = (SELECT COUNT(*) FROM @FoundUniqueIdentifierColumns)
 -- PRINT CAST(@ColumnCount as nvarchar)
 
 DECLARE @Iterator bigint
 SET @Iterator = 1
 
 -- Look through all found uniqueidentifier for the Old OrganizationId Columns and Save the TableName/ColumnName in @FoundOrganizationIds
 WHILE @Iterator <= @ColumnCount
  BEGIN
   DECLARE @execsql nvarchar(max)
   DECLARE @TableName nvarchar(max)
   DECLARE @ColumnName nvarchar(max)
   
   SET @TableName = (SELECT TableName FROM @FoundUniqueIdentifierColumns WHERE Id = @Iterator)
   SET @ColumnName = (SELECT ColumnName FROM @FoundUniqueIdentifierColumns WHERE Id = @Iterator)
   
   --PRINT(@TableName)
   --PRINT(@@ColumnName)
   
   SET @execsql = 'SELECT DISTINCT ' + CHAR(39) + @TableName + CHAR(39) + ','
   SET @execsql = @execsql + CHAR(39) + @ColumnName + CHAR(39) + ','
   SET @execsql = @execsql + @ColumnName 
   SET @execsql = @execsql + ' FROM ' 
   SET @execsql = @execsql + @TableName 
   SET @execsql = @execsql + ' WHERE '
   SET @execsql = @execsql + @ColumnName 
   SET @execsql = @execsql + ' = ' + CHAR(39) + CAST(@OldOrganizationId as varchar(50)) + CHAR(39)
   
   INSERT INTO @FoundOrganizationIds (TableName, ColumnName, ColumnValue)
   -- PRINT (@execsql)
   EXEC (@execsql)
   
   SET @Iterator = @Iterator + 1   
  END
 
 -- SELECT * FROM @FoundOrganizationIds
 
 DECLARE @ColumnIterator bigint, @ColumnTotal bigint
 SET @ColumnIterator = 1
 SET @ColumnTotal = (SELECT COUNT(id) FROM @FoundOrganizationIds)
 
 PRINT (@ColumnTotal)
 
 -- INSERT New Organization in the OrganizationTable with the new OrganizationId (Copy of the Old Organization but with the new Id)
 INSERT INTO [dbo].[OrganizationBase]
           ([OrganizationId]
           ,[Name]
           ,[UserGroupId]
           ,[PrivilegeUserGroupId]
           ,[FiscalPeriodType]
           ,[FiscalCalendarStart]
           ,[DateFormatCode]
           ,[TimeFormatCode]
           ,[CurrencySymbol]
           ,[WeekStartDayCode]
           ,[DateSeparator]
           ,[FullNameConventionCode]
           ,[NegativeFormatCode]
           ,[NumberFormat]
           ,[IsDisabled]
           ,[DisabledReason]
           ,[KbPrefix]
           ,[CurrentKbNumber]
           ,[CasePrefix]
           ,[CurrentCaseNumber]
           ,[ContractPrefix]
           ,[CurrentContractNumber]
           ,[QuotePrefix]
           ,[CurrentQuoteNumber]
           ,[OrderPrefix]
           ,[CurrentOrderNumber]
           ,[InvoicePrefix]
           ,[CurrentInvoiceNumber]
           ,[UniqueSpecifierLength]
           ,[CreatedOn]
           ,[ModifiedOn]
           ,[FiscalYearFormat]
           ,[FiscalPeriodFormat]
           ,[FiscalYearPeriodConnect]
           ,[LanguageCode]
           ,[SortId]
           ,[DateFormatString]
           ,[TimeFormatString]
           ,[PricingDecimalPrecision]
           ,[ShowWeekNumber]
           ,[NextTrackingNumber]
           ,[TagMaxAggressiveCycles]
           ,[TokenKey]
           ,[SystemUserId]
           ,[CreatedBy]
           ,[GrantAccessToNetworkService]
           ,[AllowOutlookScheduledSyncs]
           ,[AllowMarketingEmailExecution]
           ,[SqlAccessGroupId]
           ,[CurrencyFormatCode]
           ,[FiscalSettingsUpdated]
           ,[ReportingGroupId]
           ,[TokenExpiry]
           ,[ShareToPreviousOwnerOnAssign]
           ,[AcknowledgementTemplateId]
           ,[ModifiedBy]
           ,[IntegrationUserId]
           ,[TrackingTokenIdBase]
           ,[BusinessClosureCalendarId]
           ,[AllowAutoUnsubscribeAcknowledgement]
           ,[AllowAutoUnsubscribe]
           ,[Picture]
           ,[TrackingPrefix]
           ,[MinOutlookSyncInterval]
           ,[BulkOperationPrefix]
           ,[AllowAutoResponseCreation]
           ,[MaximumTrackingNumber]
           ,[CampaignPrefix]
           ,[SqlAccessGroupName]
           ,[CurrentCampaignNumber]
           ,[FiscalYearDisplayCode]
           ,[SiteMapXml]
           ,[IsRegistered]
           ,[ReportingGroupName]
           ,[CurrentBulkOperationNumber]
           ,[SchemaNamePrefix]
           ,[IgnoreInternalEmail]
           ,[TagPollingPeriod]
           ,[TrackingTokenIdDigits]
           ,[NumberGroupFormat]
           ,[LongDateFormatCode]
           ,[UTCConversionTimeZoneCode]
           ,[TimeZoneRuleVersionNumber]
           ,[CurrentImportSequenceNumber]
           ,[ParsedTablePrefix]
           ,[V3CalloutConfigHash]
           ,[IsFiscalPeriodMonthBased]
           ,[LocaleId]
           ,[ParsedTableColumnPrefix]
           ,[SupportUserId]
           ,[AMDesignator]
           ,[CurrencyDisplayOption]
           ,[MinAddressBookSyncInterval]
           ,[IsDuplicateDetectionEnabledForOnlineCreateUpdate]
           ,[FeatureSet]
           ,[BlockedAttachments]
           ,[IsDuplicateDetectionEnabledForOfflineSync]
           ,[AllowOfflineScheduledSyncs]
           ,[AllowUnresolvedPartiesOnEmailSend]
           ,[TimeSeparator]
           ,[CurrentParsedTableNumber]
           ,[MinOfflineSyncInterval]
           ,[AllowWebExcelExport]
           ,[ReferenceSiteMapXml]
           ,[IsDuplicateDetectionEnabledForImport]
           ,[CalendarType]
           ,[SQMEnabled]
           ,[NegativeCurrencyFormatCode]
           ,[AllowAddressBookSyncs]
           ,[ISVIntegrationCode]
           ,[DecimalSymbol]
           ,[MaxUploadFileSize]
           ,[IsAppMode]
           ,[EnablePricingOnCreate]
           ,[IsSOPIntegrationEnabled]
           ,[PMDesignator]
           ,[CurrencyDecimalPrecision]
           ,[MaxAppointmentDurationDays]
           ,[EmailSendPollingPeriod]
           ,[RenderSecureIFrameForEmail]
           ,[NumberSeparator]
           ,[PrivReportingGroupId]
           ,[BaseCurrencyId]
           ,[MaxRecordsForExportToExcel]
           ,[PrivReportingGroupName]
           ,[YearStartWeekCode]
           ,[IsPresenceEnabled]
           ,[IsDuplicateDetectionEnabled]
           ,[RecurrenceExpansionJobBatchInterval]
           ,[DefaultRecurrenceEndRangeType]
           ,[HashMinAddressCount]
           ,[RequireApprovalForUserEmail]
           ,[RecurrenceDefaultNumberOfOccurrences]
           ,[ModifiedOnBehalfBy]
           ,[RequireApprovalForQueueEmail]
           ,[AllowEntityOnlyAudit]
           ,[IsAuditEnabled]
           ,[RecurrenceExpansionSynchCreateMax]
           ,[GoalRollupExpiryTime]
           ,[BaseCurrencyPrecision]
           ,[FiscalPeriodFormatPeriod]
           ,[AllowClientMessageBarAd]
           ,[InitialVersion]
           ,[HashFilterKeywords]
           ,[NextCustomObjectTypeCode]
           ,[ExpireSubscriptionsInDays]
           ,[OrgDbOrgSettings]
           ,[PastExpansionWindow]
           ,[EnableSmartMatching]
           ,[MaxRecordsForLookupFilters]
           ,[BaseCurrencySymbol]
           ,[ReportScriptErrors]
           ,[RecurrenceExpansionJobBatchSize]
           ,[FutureExpansionWindow]
           ,[GetStartedPaneContentEnabled]
           ,[SampleDataImportId]
           ,[BaseISOCurrencyCode]
           ,[GoalRollupFrequency]
           ,[CreatedOnBehalfBy]
           ,[HashDeltaSubjectCount]
           ,[HashMaxCount]
           ,[FiscalYearFormatYear]
           ,[FiscalYearFormatPrefix]
           ,[PinpointLanguageCode]
           ,[FiscalYearFormatSuffix]
           ,[IsUserAccessAuditEnabled]
           ,[UserAccessAuditingInterval])
 SELECT 
    @NewOrganizationId,
    [Name]
   ,[UserGroupId]
      ,[PrivilegeUserGroupId]
      ,[FiscalPeriodType]
      ,[FiscalCalendarStart]
      ,[DateFormatCode]
      ,[TimeFormatCode]
      ,[CurrencySymbol]
      ,[WeekStartDayCode]
      ,[DateSeparator]
      ,[FullNameConventionCode]
      ,[NegativeFormatCode]
      ,[NumberFormat]
      ,[IsDisabled]
      ,[DisabledReason]
      ,[KbPrefix]
      ,[CurrentKbNumber]
      ,[CasePrefix]
      ,[CurrentCaseNumber]
      ,[ContractPrefix]
      ,[CurrentContractNumber]
      ,[QuotePrefix]
      ,[CurrentQuoteNumber]
      ,[OrderPrefix]
      ,[CurrentOrderNumber]
      ,[InvoicePrefix]
      ,[CurrentInvoiceNumber]
      ,[UniqueSpecifierLength]
      ,[CreatedOn]
      ,[ModifiedOn]
      ,[FiscalYearFormat]
      ,[FiscalPeriodFormat]
      ,[FiscalYearPeriodConnect]
      ,[LanguageCode]
      ,[SortId]
      ,[DateFormatString]
      ,[TimeFormatString]
      ,[PricingDecimalPrecision]
      ,[ShowWeekNumber]
      ,[NextTrackingNumber]
      ,[TagMaxAggressiveCycles]
      ,[TokenKey]
      ,[SystemUserId]
      ,[CreatedBy]
      ,[GrantAccessToNetworkService]
      ,[AllowOutlookScheduledSyncs]
      ,[AllowMarketingEmailExecution]
      ,[SqlAccessGroupId]
      ,[CurrencyFormatCode]
      ,[FiscalSettingsUpdated]
      ,[ReportingGroupId]
      ,[TokenExpiry]
      ,[ShareToPreviousOwnerOnAssign]
      ,[AcknowledgementTemplateId]
      ,[ModifiedBy]
      ,[IntegrationUserId]
      ,[TrackingTokenIdBase]
      ,[BusinessClosureCalendarId]
      ,[AllowAutoUnsubscribeAcknowledgement]
      ,[AllowAutoUnsubscribe]
      ,[Picture]
      ,[TrackingPrefix]
      ,[MinOutlookSyncInterval]
      ,[BulkOperationPrefix]
      ,[AllowAutoResponseCreation]
      ,[MaximumTrackingNumber]
      ,[CampaignPrefix]
      ,[SqlAccessGroupName]
      ,[CurrentCampaignNumber]
      ,[FiscalYearDisplayCode]
      ,[SiteMapXml]
      ,[IsRegistered]
      ,[ReportingGroupName]
      ,[CurrentBulkOperationNumber]
      ,[SchemaNamePrefix]
      ,[IgnoreInternalEmail]
      ,[TagPollingPeriod]
      ,[TrackingTokenIdDigits]
      ,[NumberGroupFormat]
      ,[LongDateFormatCode]
      ,[UTCConversionTimeZoneCode]
      ,[TimeZoneRuleVersionNumber]
      ,[CurrentImportSequenceNumber]
      ,[ParsedTablePrefix]
      ,[V3CalloutConfigHash]
      ,[IsFiscalPeriodMonthBased]
      ,[LocaleId]
      ,[ParsedTableColumnPrefix]
      ,[SupportUserId]
      ,[AMDesignator]
      ,[CurrencyDisplayOption]
      ,[MinAddressBookSyncInterval]
      ,[IsDuplicateDetectionEnabledForOnlineCreateUpdate]
      ,[FeatureSet]
      ,[BlockedAttachments]
      ,[IsDuplicateDetectionEnabledForOfflineSync]
      ,[AllowOfflineScheduledSyncs]
      ,[AllowUnresolvedPartiesOnEmailSend]
      ,[TimeSeparator]
      ,[CurrentParsedTableNumber]
      ,[MinOfflineSyncInterval]
      ,[AllowWebExcelExport]
      ,[ReferenceSiteMapXml]
      ,[IsDuplicateDetectionEnabledForImport]
      ,[CalendarType]
      ,[SQMEnabled]
      ,[NegativeCurrencyFormatCode]
      ,[AllowAddressBookSyncs]
      ,[ISVIntegrationCode]
      ,[DecimalSymbol]
      ,[MaxUploadFileSize]
      ,[IsAppMode]
      ,[EnablePricingOnCreate]
      ,[IsSOPIntegrationEnabled]
      ,[PMDesignator]
      ,[CurrencyDecimalPrecision]
      ,[MaxAppointmentDurationDays]
      ,[EmailSendPollingPeriod]
      ,[RenderSecureIFrameForEmail]
      ,[NumberSeparator]
      ,[PrivReportingGroupId]
      ,[BaseCurrencyId]
      ,[MaxRecordsForExportToExcel]
      ,[PrivReportingGroupName]
      ,[YearStartWeekCode]
      ,[IsPresenceEnabled]
      ,[IsDuplicateDetectionEnabled]
      ,[RecurrenceExpansionJobBatchInterval]
      ,[DefaultRecurrenceEndRangeType]
      ,[HashMinAddressCount]
      ,[RequireApprovalForUserEmail]
      ,[RecurrenceDefaultNumberOfOccurrences]
      ,[ModifiedOnBehalfBy]
      ,[RequireApprovalForQueueEmail]
      ,[AllowEntityOnlyAudit]
      ,[IsAuditEnabled]
      ,[RecurrenceExpansionSynchCreateMax]
      ,[GoalRollupExpiryTime]
      ,[BaseCurrencyPrecision]
      ,[FiscalPeriodFormatPeriod]
      ,[AllowClientMessageBarAd]
      ,[InitialVersion]
      ,[HashFilterKeywords]
      ,[NextCustomObjectTypeCode]
      ,[ExpireSubscriptionsInDays]
      ,[OrgDbOrgSettings]
      ,[PastExpansionWindow]
      ,[EnableSmartMatching]
      ,[MaxRecordsForLookupFilters]
      ,[BaseCurrencySymbol]
      ,[ReportScriptErrors]
      ,[RecurrenceExpansionJobBatchSize]
      ,[FutureExpansionWindow]
      ,[GetStartedPaneContentEnabled]
      ,[SampleDataImportId]
      ,[BaseISOCurrencyCode]
      ,[GoalRollupFrequency]
      ,[CreatedOnBehalfBy]
      ,[HashDeltaSubjectCount]
      ,[HashMaxCount]
      ,[FiscalYearFormatYear]
      ,[FiscalYearFormatPrefix]
      ,[PinpointLanguageCode]
      ,[FiscalYearFormatSuffix]
      ,[IsUserAccessAuditEnabled]
      ,[UserAccessAuditingInterval]
  FROM 
  [dbo].[OrganizationBase] 
  WHERE 
  OrganizationId = @OldOrganizationId
  
 -- Loop through the Found Columns and Update them with the new OrganizationId
 WHILE @ColumnIterator <= @ColumnTotal
  BEGIN
   DECLARE @CurrentTable nvarchar(max)
   DECLARE @CurrentColumn nvarchar(max)
   
   SET @CurrentTable = (SELECT TableName FROM @FoundOrganizationIds WHERE Id = @ColumnIterator)
   SET @CurrentColumn = (SELECT ColumnName FROM @FoundOrganizationIds WHERE Id = @ColumnIterator)
   
   --PRINT (@CurrentTable)
   --PRINT (@CurrentColumn)
   
   -- Skip the OrganizationBase table now, since we have allready added the new OrganizationId 
   IF @CurrentTable <> 'OrganizationBase'
    BEGIN
     DECLARE @UpdateScript nvarchar(max)
     SET @UpdateScript = ' UPDATE ' + @CurrentTable + ' SET ' + @CurrentColumn + ' = ' + CHAR(39) + CAST(@NewOrganizationId as varchar(50)) + CHAR(39) + ' WHERE ' + @CurrentColumn + ' = ' + CHAR(39) + CAST(@OldOrganizationId as varchar(50))+ CHAR(39)
     -- PRINT (@UpdateScript)
     EXEC (@UpdateScript)
    END 
   SET @ColumnIterator = @ColumnIterator + 1
  END
 
 -- Delete the Old Organization from the OrganizationBase
 DELETE FROM OrganizationBase WHERE OrganizationId = @OldOrganizationId

After applying this script, closing SQL Server Management Studio and trying again the import process, it all worked like a charm.Smile.

 

Hope it helps to save somebody else’s time.

 

PP [twitter: @pabloperalta]

UruIT Dynamix | Excellence in Dynamics CRM Nearshoring Services.

uruit_dynamix_016[2]

No Comments