locked
CRM 2011 Import Organization on the same server (or how do you create a development sandboxes)

    Question

  • We have a Development CRM 2011 server that includes everything (CRM, SQL, SSRS, etc)  We have several orgs on it.  one of the orgs (CRM01DEV) is our primary org we use for development.  I want to create a copy of it so I can do a proof of concept on some changes.  I thought I would be able to use the "Import Organization..."  in the Deployment Manager.  When I tried I got the following error;

    Error while updating organization information: System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessUnitExtensionBase_BusinessUnitBase". The conflict occurred in database "CRM01Dev_MSCRM", table "dbo.BusinessUnitExtensionBase", column 'BusinessUnitId'.
    The statement has been terminated.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
       at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
       at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate, Boolean capturePerfTrace)
       at Microsoft.Crm.Tools.Admin.DBImportHelper.UpdateBusinessReferences(IDbCommand command)
       at Microsoft.Crm.Tools.Admin.ImportOrganizationInstaller.UpdateOrganizationInfo(Guid organizationId, OrganizationGroupsInfo organizationInfo, String organizationFriendlyName, String organizationUniqueName, Uri reportServerUrl, Int32 PercentUpdateOrganization, ICollection`1 users)

    I know I could create a new org and then import the solution from CRM01DEV, but I wouldn't have any test data.

    How are others creating sandboxes for development?

     

    Sunday, March 13, 2011 5:55 PM

Answers

  • Hi Serge,

     

    dont use that script anymore, it's not correct. It will update the organizationid column in the entire Database but that is not enough. There is another reference in the PrincipalObjectAccess Table the Column "PrincipalId". In your case you have to update this Column in order your email router and other things to work.

    As for changing future OrganizationId's do the following:

    Make sure you have write Access to the OrganizationBase table and just execute this script. It will create a new record in the OrganizationBase Table (copy of the old Organization but with the new OrganizationId), then update the references to the old OrganizationId and at the end will delete the old Organization), So you will be left with the new OrgId and all References updated in the entire Database, not by Column Name as suggested from the other comment.


     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

     

     

     

     

     


    B. Kasami
    • Proposed as answer by frenkie smart Sunday, December 11, 2011 12:51 AM
    • Marked as answer by Eric Labashosky Thursday, February 2, 2012 7:56 PM
    Sunday, December 11, 2011 12:46 AM
  • Hello, if you want to import same organization to the same server, you have to change organizationid within whole database (before you import it to crm), this is the sql script to change the organizationid:


    SELECT 'ALTER TABLE '+QUOTENAME(name)+' NOCHECK CONSTRAINT ALL'
    FROM sysobjects WHERE xtype='U' and uid=1

    declare @newid uniqueidentifier

    select @newid = newid()

     SELECT 
            'Update ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ' set ' + c.COLUMN_NAME +' =''' + +cast(@newid as varchar(40))+''''
        FROM INFORMATION_SCHEMA.Columns c
            INNER JOIN INFORMATION_SCHEMA.Tables t
            ON c.TABLE_NAME = t.TABLE_NAME
            AND t.TABLE_TYPE = 'BASE TABLE'
        WHERE DATA_TYPE = 'uniqueidentifier'  and column_name='organizationid'
       
    SELECT 'ALTER TABLE '+QUOTENAME(name)+' CHECK CONSTRAINT ALL'
    FROM sysobjects WHERE xtype='U' and uid=1

    These 3 scripts return set of sql scripts which must be executed to change te organizationid.

    After this change you can import the organization to CRM.


    Pavel Brestovsky
    • Proposed as answer by Mark.Cherry Friday, October 21, 2011 8:31 AM
    • Marked as answer by Eric Labashosky Thursday, February 2, 2012 7:03 PM
    Friday, June 3, 2011 1:50 PM

All replies

  • I have an idea that I have not tested for 2011 but does work for 4.0.  If you make a copy of the database and change the name it is mounted as you can basically change the name of your organization which should allow you to import it through deployment manager. 

    Make sure it is a copy of your original org (different MDF file) as it could be risky and I would hate to have you corrupt your main org database in some way.


    Jamie Miley
    http://mileyja.blogspot.com
    LinkedIn Profile
    Monday, March 14, 2011 2:27 AM
    Moderator
  • If you have have customized the BusinessUnit entity by adding new attributes to it then you should probably get this exception during "Import Organization...".

    To validate if this is indeed your case, please check the BusinessUnitExtensionBase table and if you see there other columns besides the BusinessUnitId column then the entity has been customized. And if you run query "select * from BusinessUnitExtensionBase", you should get some rows.

    Please let me know if this indeed is your case.

    This is a product bug and I have opened a bug to track it.

    The only workaround I can think of is this: you need to delete all custom attribute that you have defined for the BusinessUnit entity. To do this follow these steps. You may not be able to do it though because you need to alter the production data.

    1) Backup your production CRM01DEV org db and MSCRM_CONFIG db. You will need them later in order to restore back the production dbs.

    2) On your production organization go to the Settings (left navigation panel)->Customization->Customize the System. Open the BusinessUnit entity->go to Fields and delete all custom attributes that have been defined there. Save changes. Publish changes.

    3) Verify that all custom attributes of BusinessUnit entity are gone.
    Run this query. If it returns rows then you still have custom attributes that need to be deleted. Go back to step 2.
     select * from AttributeView where IsCustomField = 1 and EntityId in (select EntityId from EntityView where Name = 'BusinessUnit')

    One other way to verify this is by going directly to the SQl Server and checking on the BusinessUnitExtensionBase table that all columns except for the the BusinessUnitId are gone. If there are still more columns in there you didn't delete all custom attributes. Go back to step 2.

    4) Once all custom attributs are deleted (you have only the BusinessUnitId column in the BusinessUnitExtensionBase table), check if there are still any records in this table. Run this query:
     select * from BusinessUnitExtensionBase

    If any rows are returned then delete them all by executing this:
     delete from BusinessUnitExtensionBase

    5) Backup this altered production org to a different file name than the one you have taken in step 1.

    6) Delete the current production org db from SQL Server (the one we have altered).

    7) Restore back the original dbs you have taken backups in step 1. This should restore your production data as it was before we have started to alter it.

    8) Restore db using the backup file you have taken in step 5, but use a new (different) org db name.

    9) Go to Deployment Manager -> Import Organization and try to import the the db that you have restored at step 8. Let's hope that now the Import works.


    Please don't forget to take backups of all the original production dbs (step 1) so you can restore them later at step 7 !!!

    Let me know if it works or not.


    Adrian Omusoru Microsoft
    Tuesday, March 15, 2011 8:28 AM
  • Hello, if you want to import same organization to the same server, you have to change organizationid within whole database (before you import it to crm), this is the sql script to change the organizationid:


    SELECT 'ALTER TABLE '+QUOTENAME(name)+' NOCHECK CONSTRAINT ALL'
    FROM sysobjects WHERE xtype='U' and uid=1

    declare @newid uniqueidentifier

    select @newid = newid()

     SELECT 
            'Update ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ' set ' + c.COLUMN_NAME +' =''' + +cast(@newid as varchar(40))+''''
        FROM INFORMATION_SCHEMA.Columns c
            INNER JOIN INFORMATION_SCHEMA.Tables t
            ON c.TABLE_NAME = t.TABLE_NAME
            AND t.TABLE_TYPE = 'BASE TABLE'
        WHERE DATA_TYPE = 'uniqueidentifier'  and column_name='organizationid'
       
    SELECT 'ALTER TABLE '+QUOTENAME(name)+' CHECK CONSTRAINT ALL'
    FROM sysobjects WHERE xtype='U' and uid=1

    These 3 scripts return set of sql scripts which must be executed to change te organizationid.

    After this change you can import the organization to CRM.


    Pavel Brestovsky
    • Proposed as answer by Mark.Cherry Friday, October 21, 2011 8:31 AM
    • Marked as answer by Eric Labashosky Thursday, February 2, 2012 7:03 PM
    Friday, June 3, 2011 1:50 PM
  • Does it mean that I need to execute all script in the 3 results set ?

    ALL the ALTER scrip from first result
    then all Update script from second result

    Then the third

    Is that correct ?

    If it is, thaty does not work for me.

     

    What I am trying to do is the follwoing :

    Create a duplicate deplopyement of a running deploiement. Doing so I create a new deployement with a new name which create an empty database.
    Then I restore a copy of the original organisation database.

    But when  doing the import organisation, the wizard could not find a single organisation on the same server for import

     

     

    regards

    serge


    Your knowledge is enhanced by that of others.
    Wednesday, August 24, 2011 5:38 AM
  • Yes, it is correct. The whole process is:

    1. backup original database
    2. restore the original database to new one
    3. run this script on new database
    4. run the 3 results on new database
    5. import organization from new database with different name than original organization

    This method was working for me everytime (3 or 4 times)


    Pavel Brestovsky
    Wednesday, August 24, 2011 6:09 AM
  • works great,

    I should spot it on my french blob to remember :-)

    thanks


    Your knowledge is enhanced by that of others.
    Wednesday, August 24, 2011 4:47 PM
  • Hello pavel,

    As you seems to have great expeience on crm are you able to help on following post :

    http://social.microsoft.com/Forums/en/crmdevelopment/thread/78eda31c-08d0-4a7f-ae05-4a90d96bed6d

     

    regardss

    serge


    Your knowledge is enhanced by that of others.
    Thursday, August 25, 2011 6:57 AM
  • Hello,

    I have a strange issue after doing this procedure you mentionned.
    What happen is that my User, Queue and forward mail box can not be loaded from Email router settings ?

    HOw can I be sure that Organisation name is correct in every table after running those scrips?


    Your knowledge is enhanced by that of others.
    Monday, August 29, 2011 7:06 PM
  • Hello, this script desnt change organization name, it changes organization id. It doesnt affect the original organization (and its setting on email router), you have to execute the script on the copy of organization before you import it in deployment manager.

    I didnt try to set up email router for copy of organization.


    Pavel Brestovsky
    Monday, August 29, 2011 7:22 PM
  •  days that I try to configured email router after the copy and no luck so far
    Your knowledge is enhanced by that of others.
    Monday, August 29, 2011 7:56 PM
  • are you able t o make a test with email router for a cpy of organisation?
    Your knowledge is enhanced by that of others.
    Wednesday, August 31, 2011 1:47 PM
  • Hi Serge,

     

    dont use that script anymore, it's not correct. It will update the organizationid column in the entire Database but that is not enough. There is another reference in the PrincipalObjectAccess Table the Column "PrincipalId". In your case you have to update this Column in order your email router and other things to work.

    As for changing future OrganizationId's do the following:

    Make sure you have write Access to the OrganizationBase table and just execute this script. It will create a new record in the OrganizationBase Table (copy of the old Organization but with the new OrganizationId), then update the references to the old OrganizationId and at the end will delete the old Organization), So you will be left with the new OrgId and all References updated in the entire Database, not by Column Name as suggested from the other comment.


     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

     

     

     

     

     


    B. Kasami
    • Proposed as answer by frenkie smart Sunday, December 11, 2011 12:51 AM
    • Marked as answer by Eric Labashosky Thursday, February 2, 2012 7:56 PM
    Sunday, December 11, 2011 12:46 AM
  • Thanks guys!!!!!

    I've been using Pavel Brestovsky for a while without any problem.  But today I switched over today to use frenkie smart(aka B Kasami??) script.

    I did have a problem with the email router, but that was due to the fact that the new org didn't have a DNS(or host file) entry  (required for IFD).

    Thursday, February 2, 2012 8:01 PM
  • What happens if you delete the organization in Deployment Manager, import another copy of the 4.0 organization and the do an import of the deleted organization in Deployment Manager?
    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    Friday, February 3, 2012 8:08 AM
  • Hi Frenkie,

    your script wil change/update the organizationid of the organization database. what about the MSCRM_CONFIG database. the config database is still holding the old organizationid. is it not required to change that one too?

    Tuesday, April 10, 2012 9:13 PM
  • Thank you Frankie.

    This script worked like a charm for us :).

    PP


    Microsoft MVP Dynamics CRM | My Twitter: http://twitter.com/pabloperalta | My blog: http://weblogs.asp.net/pabloperalta | Blog en Español: http://wwww.elblogdedynamicscrm.com

    Friday, June 15, 2012 7:05 PM
  • Great question. I agree and wonder the same thing.

    EDIT: ah ha, I see. This instruction is so that you can run an import of an existing company. So the company shouldn't exist in Deployment Manager yet at all, these instructions are just the steps for preparation of the database up to the actual import in deployment manager.

    • Edited by mark.0 Thursday, August 16, 2012 9:47 PM
    Thursday, August 16, 2012 9:44 PM
  • Awesome Solution for CRM 2011 organizations. Does the script work on CRM 2013 and CRM 2015 organizations as well?
    Wednesday, March 4, 2015 11:26 PM
  • I havent tested this in CRM 2013. This way of changing the Organization Id should work on CRM 2013 too. You just have to make sure that the Organization table contains the same columns like in the script. If you notice that additional columns exist on the Org. Table, then you have to extend the script to add the missing resp. Newly added columns. What this method does is, it copies the entire row in the organization table (then you have a second row with a new Organization Id and the rest of the information stays the same), then it updates all references in all tables that reference the old OrgId to reference the new OrgId, and last but not least it deletes the old Organization Row from the table. After this you can import the Organization and should have no conflicts with the old one, because it will be considered like a different/new Organization.

    B. Kasami

    Thursday, March 5, 2015 9:56 PM
  • While B. Kasami's script did not work for me in 2015 the script from Pavel Brestovsky did work like a charm to allow me to create our dev and test environments from our production environment.

    Thanks to you both for all you do!

    Jenn

    Thursday, September 3, 2015 3:33 PM
  • Do you have a copy of that script I have been reading this and they all say not to use them.  I copied production to a new sandbox DB and need it to work.

    Thank You

    Monday, November 16, 2015 10:37 PM
  • I havent tested this in CRM 2013. This way of changing the Organization Id should work on CRM 2013 too. You just have to make sure that the Organization table contains the same columns like in the script. If you notice that additional columns exist on the Org. Table, then you have to extend the script to add the missing resp. Newly added columns. What this method does is, it copies the entire row in the organization table (then you have a second row with a new Organization Id and the rest of the information stays the same), then it updates all references in all tables that reference the old OrgId to reference the new OrgId, and last but not least it deletes the old Organization Row from the table. After this you can import the Organization and should have no conflicts with the old one, because it will be considered like a different/new Organization.

    B. Kasami

    I have tested this in CRM 2015. This way of changing the Organization ID should work on CRM 2015 too.
    You can get the entire rows, if you use the normal "Select Top 1000 Rows" action from the SQL Management Studio-Context-Menu.

    But you have to delete the column "VersionNumber".

    Monday, December 14, 2015 12:10 PM
  • Just tested on 2016. It works.
    Monday, July 4, 2016 6:02 AM
  • All- here is the script updated from CRM 2016 SP1

    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]
               ,[AllowUserFormModePreference]
               ,[MaximumActiveBusinessProcessFlowsAllowedPerEntity]
               ,[YammerGroupId]
               ,[IsUserAccessAuditEnabled]
               ,[IsDefaultCountryCodeCheckEnabled]
               ,[MetadataSyncLastTimeOfNeverExpiredDeletedObjects]
               ,[IncomingEmailExchangeEmailRetrievalBatchSize]
               ,[QuickFindRecordLimitEnabled]
               ,[YammerOAuthAccessTokenExpired]
               ,[UseSkypeProtocol]
               ,[DefaultEmailServerProfileId]
               ,[GenerateAlertsForErrors]
               ,[DefaultCountryCode]
               ,[EmailCorrelationEnabled]
               ,[UserAccessAuditingInterval]
               ,[IsAutoSaveEnabled]
               ,[DefaultEmailSettings]
               ,[MetadataSyncTimestamp]
               ,[YammerNetworkPermalink]
               ,[EmailConnectionChannel]
               ,[EntityImageId]
               ,[NotifyMailboxOwnerOfEmailServerLevelAlerts]
               ,[BingMapsApiKey]
               ,[GenerateAlertsForInformation]
               ,[GenerateAlertsForWarnings]
               ,[YammerPostMethod]
               ,[AllowUsersSeeAppdownloadMessage]
               ,[UseReadForm]
               ,[EnableBingMapsIntegration]
               ,[DisableSocialCare]
               ,[SocialInsightsInstance]
               ,[UseInbuiltRuleForDefaultPricelistSelection]
               ,[DiscountCalculationMethod]
               ,[IsAssignedTasksSyncEnabled]
               ,[IsAppointmentAttachmentSyncEnabled]
               ,[IsContactMailingAddressSyncEnabled]
               ,[MaxSupportedInternetExplorerVersion]
               ,[CreateProductsWithoutParentInActiveState]
               ,[SlaPauseStates]
               ,[MaxProductsInBundle]
               ,[OOBPriceCalculationEnabled]
               ,[CascadeStatusUpdate]
               ,[RestrictStatusUpdate]
               ,[MaxDepthForHierarchicalSecurityModel]
               ,[IsHierarchicalSecurityModelEnabled]
               ,[GlobalHelpUrlEnabled]
               ,[DisplayNavigationTour]
               ,[GlobalAppendUrlParametersEnabled]
               ,[SocialInsightsTermsAccepted]
               ,[SocialInsightsEnabled]
               ,[GlobalHelpUrl]
               ,[SignupOutlookDownloadFWLink]
               ,[MaximumDynamicPropertiesAllowed]
               ,[UsePositionHierarchy]
               ,[SuppressSLA]
               ,[IsMailboxInactiveBackoffEnabled]
               ,[MobileOfflineSyncInterval]
               ,[PluginTraceLogSetting]
               ,[DaysSinceRecordLastModifiedMaxValue]
               ,[MaxFolderBasedTrackingMappings]
               ,[KaPrefix]
               ,[SharePointDeploymentType]
               ,[TaskBasedFlowEnabled]
               ,[MailboxPermanentIssueMinRange]
               ,[IsOneDriveEnabled]
               ,[MaxVerboseLoggingSyncCycles]
               ,[OfficeGraphDelveUrl]
               ,[WebResourceHash]
               ,[MaxVerboseLoggingMailbox]
               ,[IsFullTextSearchEnabled]
               ,[DefaultMobileOfflineProfileId]
               ,[AutoApplyDefaultonCaseCreate]
               ,[IsDelegateAccessEnabled]
               ,[KMSettings]
               ,[MobileOfflineMinLicenseTrial]
               ,[DefaultThemeData]
               ,[AutoApplySLA]
               ,[MailboxIntermittentIssueMinRange]
               ,[HighContrastThemeData]
               ,[IsFolderBasedTrackingEnabled]
               ,[PrivacyStatementUrl]
               ,[MobileOfflineMinLicenseProd]
               ,[ExpireChangeTrackingInDays]
               ,[ExternalPartyCorrelationKeys]
               ,[ShowKBArticleDeprecationNotification]
               ,[IsMailboxForcedUnlockingEnabled]
               ,[ExternalPartyEntitySettings]
               ,[IsOfficeGraphEnabled]
               ,[IsExternalSearchIndexEnabled]
               ,[CurrentKaNumber]
               ,[DelegatedAdminUserId]
               ,[EnforceReadOnlyPlugins]
               ,[IsEmailServerProfileContentFilteringEnabled]
               ,[UseLegacyRendering]
               ,[IsMobileOfflineEnabled]
               ,[AzureSchedulerJobCollectionName]
               ,[AutoApplyDefaultonCaseUpdate]
               ,[MaximumEntitiesWithActiveSLA]
               ,[CortanaProactiveExperienceEnabled]
               ,[OfficeAppsAutoDeploymentEnabled]
               ,[ProductRecommendationsEnabled]
               ,[PowerBiFeatureEnabled]
               ,[CurrentCategoryNumber]
               ,[IsDelveActionHubIntegrationEnabled]
               ,[CategoryPrefix]
               ,[IsFolderAutoCreatedonSP]
               ,[TextAnalyticsEnabled]
               ,[IsConflictDetectionEnabledForMobileClient]
               ,[MaximumSLAKPIPerEntityWithActiveSLA]
               ,[MaxConditionsForMobileOfflineFilters]
               ,[OrgInsightsEnabled])
     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]
               ,[AllowUserFormModePreference]
               ,[MaximumActiveBusinessProcessFlowsAllowedPerEntity]
               ,[YammerGroupId]
               ,[IsUserAccessAuditEnabled]
               ,[IsDefaultCountryCodeCheckEnabled]
               ,[MetadataSyncLastTimeOfNeverExpiredDeletedObjects]
               ,[IncomingEmailExchangeEmailRetrievalBatchSize]
               ,[QuickFindRecordLimitEnabled]
               ,[YammerOAuthAccessTokenExpired]
               ,[UseSkypeProtocol]
               ,[DefaultEmailServerProfileId]
               ,[GenerateAlertsForErrors]
               ,[DefaultCountryCode]
               ,[EmailCorrelationEnabled]
               ,[UserAccessAuditingInterval]
               ,[IsAutoSaveEnabled]
               ,[DefaultEmailSettings]
               ,[MetadataSyncTimestamp]
               ,[YammerNetworkPermalink]
               ,[EmailConnectionChannel]
               ,[EntityImageId]
               ,[NotifyMailboxOwnerOfEmailServerLevelAlerts]
               ,[BingMapsApiKey]
               ,[GenerateAlertsForInformation]
               ,[GenerateAlertsForWarnings]
               ,[YammerPostMethod]
               ,[AllowUsersSeeAppdownloadMessage]
               ,[UseReadForm]
               ,[EnableBingMapsIntegration]
               ,[DisableSocialCare]
               ,[SocialInsightsInstance]
               ,[UseInbuiltRuleForDefaultPricelistSelection]
               ,[DiscountCalculationMethod]
               ,[IsAssignedTasksSyncEnabled]
               ,[IsAppointmentAttachmentSyncEnabled]
               ,[IsContactMailingAddressSyncEnabled]
               ,[MaxSupportedInternetExplorerVersion]
               ,[CreateProductsWithoutParentInActiveState]
               ,[SlaPauseStates]
               ,[MaxProductsInBundle]
               ,[OOBPriceCalculationEnabled]
               ,[CascadeStatusUpdate]
               ,[RestrictStatusUpdate]
               ,[MaxDepthForHierarchicalSecurityModel]
               ,[IsHierarchicalSecurityModelEnabled]
               ,[GlobalHelpUrlEnabled]
               ,[DisplayNavigationTour]
               ,[GlobalAppendUrlParametersEnabled]
               ,[SocialInsightsTermsAccepted]
               ,[SocialInsightsEnabled]
               ,[GlobalHelpUrl]
               ,[SignupOutlookDownloadFWLink]
               ,[MaximumDynamicPropertiesAllowed]
               ,[UsePositionHierarchy]
               ,[SuppressSLA]
               ,[IsMailboxInactiveBackoffEnabled]
               ,[MobileOfflineSyncInterval]
               ,[PluginTraceLogSetting]
               ,[DaysSinceRecordLastModifiedMaxValue]
               ,[MaxFolderBasedTrackingMappings]
               ,[KaPrefix]
               ,[SharePointDeploymentType]
               ,[TaskBasedFlowEnabled]
               ,[MailboxPermanentIssueMinRange]
               ,[IsOneDriveEnabled]
               ,[MaxVerboseLoggingSyncCycles]
               ,[OfficeGraphDelveUrl]
               ,[WebResourceHash]
               ,[MaxVerboseLoggingMailbox]
               ,[IsFullTextSearchEnabled]
               ,[DefaultMobileOfflineProfileId]
               ,[AutoApplyDefaultonCaseCreate]
               ,[IsDelegateAccessEnabled]
               ,[KMSettings]
               ,[MobileOfflineMinLicenseTrial]
               ,[DefaultThemeData]
               ,[AutoApplySLA]
               ,[MailboxIntermittentIssueMinRange]
               ,[HighContrastThemeData]
               ,[IsFolderBasedTrackingEnabled]
               ,[PrivacyStatementUrl]
               ,[MobileOfflineMinLicenseProd]
               ,[ExpireChangeTrackingInDays]
               ,[ExternalPartyCorrelationKeys]
               ,[ShowKBArticleDeprecationNotification]
               ,[IsMailboxForcedUnlockingEnabled]
               ,[ExternalPartyEntitySettings]
               ,[IsOfficeGraphEnabled]
               ,[IsExternalSearchIndexEnabled]
               ,[CurrentKaNumber]
               ,[DelegatedAdminUserId]
               ,[EnforceReadOnlyPlugins]
               ,[IsEmailServerProfileContentFilteringEnabled]
               ,[UseLegacyRendering]
               ,[IsMobileOfflineEnabled]
               ,[AzureSchedulerJobCollectionName]
               ,[AutoApplyDefaultonCaseUpdate]
               ,[MaximumEntitiesWithActiveSLA]
               ,[CortanaProactiveExperienceEnabled]
               ,[OfficeAppsAutoDeploymentEnabled]
               ,[ProductRecommendationsEnabled]
               ,[PowerBiFeatureEnabled]
               ,[CurrentCategoryNumber]
               ,[IsDelveActionHubIntegrationEnabled]
               ,[CategoryPrefix]
               ,[IsFolderAutoCreatedonSP]
               ,[TextAnalyticsEnabled]
               ,[IsConflictDetectionEnabledForMobileClient]
               ,[MaximumSLAKPIPerEntityWithActiveSLA]
               ,[MaxConditionsForMobileOfflineFilters]
               ,[OrgInsightsEnabled]
      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
    
     

    Tuesday, August 8, 2017 9:55 PM