Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
CRM 2011 Import Organization on the same server (or how do you create a development sandboxes)

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

  • Sunday, March 13, 2011 5:55 PM
     
     
    Sign In to Vote
    0

    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?

     

     

All Replies

  • Monday, March 14, 2011 2:27 AM
    Moderator
     
     
    Sign In to Vote
    0

    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
     
  • Tuesday, March 15, 2011 8:28 AM
     
     
    Sign In to Vote
    0

    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
     
  • Friday, June 03, 2011 1:50 PM
     
     Answered
    Sign In to Vote
    1

    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 02, 2012 7:03 PM
    •  
     
  • Wednesday, August 24, 2011 5:38 AM
     
     
    Sign In to Vote
    0

    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 6:09 AM
     
     Proposed Answer
    Sign In to Vote
    0

    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 4:47 PM
     
     
    Sign In to Vote
    0

    works great,

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

    thanks

    Your knowledge is enhanced by that of others.
     
  • Thursday, August 25, 2011 6:57 AM
     
     
    Sign In to Vote
    0

    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.
     
  • Monday, August 29, 2011 7:06 PM
     
     
    Sign In to Vote
    0

    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:22 PM
     
     
    Sign In to Vote
    0

    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:56 PM
     
     
    Sign In to Vote
    0
     days that I try to configured email router after the copy and no luck so far
    Your knowledge is enhanced by that of others.
     
  • Wednesday, August 31, 2011 1:47 PM
     
     
    Sign In to Vote
    0
    are you able t o make a test with email router for a cpy of organisation?
    Your knowledge is enhanced by that of others.
     
  • Sunday, December 11, 2011 12:46 AM
     
     Answered
    Sign In to Vote
    7

    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 02, 2012 7:56 PM
    •  
     
  • Thursday, February 02, 2012 8:01 PM
     
     
    Sign In to Vote
    0

    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).

     
  • Friday, February 03, 2012 8:08 AM
     
     
    Sign In to Vote
    0
    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
     
  • Tuesday, April 10, 2012 9:13 PM
     
     
    Sign In to Vote
    0

    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?

     
  • Friday, June 15, 2012 7:05 PM
     
     
    Sign In to Vote
    0

    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

     
  • Thursday, August 16, 2012 9:44 PM
     
     
    Sign In to Vote
    0

    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
    •