Today I had an issue at the customer side when we tried to add a second CRM instance to the CRM 2013 Outlook Client. The second instance was a database copy of the original CRM instance which we planned to use as our Test (Q) system.
But as soon as we tried to test the connection to the Q instance we got the following error message:
“Cannot find any organizations on the server. Try entering the URL again. If the problem persists, contact your system administrator.”
The issue with copy instances is that the Organization ID of the OrganizationBase table will always stay the same, no matter if you import the instance with the Import function of the deployment manager.
The solution is an unsupported SQL script that can update all references to the organization id as well as id itself. Thanks to frenkie smart who posted a brilliant script for the old CRM 2011 version and which you can find here.
I modified the script so that it will copy the OrganizationBase entry with all the new columns that CRM 2013 offers.
I tried the script and so far it is working fine! And the customer is now able to have both instances Q and P in one Outlook CRM Client. SWEET!
But as soon as we tried to test the connection to the Q instance we got the following error message:
“Cannot find any organizations on the server. Try entering the URL again. If the problem persists, contact your system administrator.”
The issue with copy instances is that the Organization ID of the OrganizationBase table will always stay the same, no matter if you import the instance with the Import function of the deployment manager.
The solution is an unsupported SQL script that can update all references to the organization id as well as id itself. Thanks to frenkie smart who posted a brilliant script for the old CRM 2011 version and which you can find here.
I modified the script so that it will copy the OrganizationBase entry with all the new columns that CRM 2013 offers.
I tried the script and so far it is working fine! And the customer is now able to have both instances Q and P in one Outlook CRM Client. SWEET!
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]
,[DefaultRecurrenceEndRangeType]
,[InitialVersion]
,[HashFilterKeywords]
,[RecurrenceDefaultNumberOfOccurrences]
,[HashMinAddressCount]
,[AllowClientMessageBarAd]
,[ModifiedOnBehalfBy]
,[RequireApprovalForQueueEmail]
,[AllowEntityOnlyAudit]
,[RequireApprovalForUserEmail]
,[RecurrenceExpansionSynchCreateMax]
,[IsAuditEnabled]
,[GoalRollupExpiryTime]
,[BaseCurrencyPrecision]
,[FutureExpansionWindow]
,[FiscalPeriodFormatPeriod]
,[BaseISOCurrencyCode]
,[NextCustomObjectTypeCode]
,[ExpireSubscriptionsInDays]
,[OrgDbOrgSettings]
,[PastExpansionWindow]
,[EnableSmartMatching]
,[MaxRecordsForLookupFilters]
,[HashMaxCount]
,[ReportScriptErrors]
,[RecurrenceExpansionJobBatchSize]
,[GetStartedPaneContentEnabled]
,[SampleDataImportId]
,[PinpointLanguageCode]
,[CreatedOnBehalfBy]
,[HashDeltaSubjectCount]
,[GoalRollupFrequency]
,[FiscalYearFormatYear]
,[FiscalYearFormatPrefix]
,[BaseCurrencySymbol]
,[RecurrenceExpansionJobBatchInterval]
,[FiscalYearFormatSuffix]
,[IsUserAccessAuditEnabled]
,[UserAccessAuditingInterval]
,[AllowUserFormModePreference]
,[YammerGroupId]
,[IsDefaultCountryCodeCheckEnabled]
,[MetadataSyncLastTimeOfNeverExpiredDeletedObjects]
,[QuickFindRecordLimitEnabled]
,[YammerOAuthAccessTokenExpired]
,[UseSkypeProtocol]
,[DefaultCountryCode]
,[MetadataSyncTimestamp]
,[YammerNetworkPermalink]
,[YammerPostMethod]
,[UseReadForm]
,[EnableBingMapsIntegration]
,[MaximumActiveBusinessProcessFlowsAllowedPerEntity]
,[IncomingEmailExchangeEmailRetrievalBatchSize]
,[NotifyMailboxOwnerOfEmailServerLevelAlerts]
,[DefaultEmailServerProfileId]
,[GenerateAlertsForErrors]
,[EmailCorrelationEnabled]
,[IsAutoSaveEnabled]
,[DefaultEmailSettings]
,[EntityImageId]
,[EmailConnectionChannel]
,[BingMapsApiKey]
,[GenerateAlertsForInformation]
,[GenerateAlertsForWarnings]
,[AllowUsersSeeAppdownloadMessage]
,[SignupOutlookDownloadFWLink])
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]
,[DefaultRecurrenceEndRangeType]
,[InitialVersion]
,[HashFilterKeywords]
,[RecurrenceDefaultNumberOfOccurrences]
,[HashMinAddressCount]
,[AllowClientMessageBarAd]
,[ModifiedOnBehalfBy]
,[RequireApprovalForQueueEmail]
,[AllowEntityOnlyAudit]
,[RequireApprovalForUserEmail]
,[RecurrenceExpansionSynchCreateMax]
,[IsAuditEnabled]
,[GoalRollupExpiryTime]
,[BaseCurrencyPrecision]
,[FutureExpansionWindow]
,[FiscalPeriodFormatPeriod]
,[BaseISOCurrencyCode]
,[NextCustomObjectTypeCode]
,[ExpireSubscriptionsInDays]
,[OrgDbOrgSettings]
,[PastExpansionWindow]
,[EnableSmartMatching]
,[MaxRecordsForLookupFilters]
,[HashMaxCount]
,[ReportScriptErrors]
,[RecurrenceExpansionJobBatchSize]
,[GetStartedPaneContentEnabled]
,[SampleDataImportId]
,[PinpointLanguageCode]
,[CreatedOnBehalfBy]
,[HashDeltaSubjectCount]
,[GoalRollupFrequency]
,[FiscalYearFormatYear]
,[FiscalYearFormatPrefix]
,[BaseCurrencySymbol]
,[RecurrenceExpansionJobBatchInterval]
,[FiscalYearFormatSuffix]
,[IsUserAccessAuditEnabled]
,[UserAccessAuditingInterval]
,[AllowUserFormModePreference]
,[YammerGroupId]
,[IsDefaultCountryCodeCheckEnabled]
,[MetadataSyncLastTimeOfNeverExpiredDeletedObjects]
,[QuickFindRecordLimitEnabled]
,[YammerOAuthAccessTokenExpired]
,[UseSkypeProtocol]
,[DefaultCountryCode]
,[MetadataSyncTimestamp]
,[YammerNetworkPermalink]
,[YammerPostMethod]
,[UseReadForm]
,[EnableBingMapsIntegration]
,[MaximumActiveBusinessProcessFlowsAllowedPerEntity]
,[IncomingEmailExchangeEmailRetrievalBatchSize]
,[NotifyMailboxOwnerOfEmailServerLevelAlerts]
,[DefaultEmailServerProfileId]
,[GenerateAlertsForErrors]
,[EmailCorrelationEnabled]
,[IsAutoSaveEnabled]
,[DefaultEmailSettings]
,[EntityImageId]
,[EmailConnectionChannel]
,[BingMapsApiKey]
,[GenerateAlertsForInformation]
,[GenerateAlertsForWarnings]
,[AllowUsersSeeAppdownloadMessage]
,[SignupOutlookDownloadFWLink]
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
-- 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]
,[DefaultRecurrenceEndRangeType]
,[InitialVersion]
,[HashFilterKeywords]
,[RecurrenceDefaultNumberOfOccurrences]
,[HashMinAddressCount]
,[AllowClientMessageBarAd]
,[ModifiedOnBehalfBy]
,[RequireApprovalForQueueEmail]
,[AllowEntityOnlyAudit]
,[RequireApprovalForUserEmail]
,[RecurrenceExpansionSynchCreateMax]
,[IsAuditEnabled]
,[GoalRollupExpiryTime]
,[BaseCurrencyPrecision]
,[FutureExpansionWindow]
,[FiscalPeriodFormatPeriod]
,[BaseISOCurrencyCode]
,[NextCustomObjectTypeCode]
,[ExpireSubscriptionsInDays]
,[OrgDbOrgSettings]
,[PastExpansionWindow]
,[EnableSmartMatching]
,[MaxRecordsForLookupFilters]
,[HashMaxCount]
,[ReportScriptErrors]
,[RecurrenceExpansionJobBatchSize]
,[GetStartedPaneContentEnabled]
,[SampleDataImportId]
,[PinpointLanguageCode]
,[CreatedOnBehalfBy]
,[HashDeltaSubjectCount]
,[GoalRollupFrequency]
,[FiscalYearFormatYear]
,[FiscalYearFormatPrefix]
,[BaseCurrencySymbol]
,[RecurrenceExpansionJobBatchInterval]
,[FiscalYearFormatSuffix]
,[IsUserAccessAuditEnabled]
,[UserAccessAuditingInterval]
,[AllowUserFormModePreference]
,[YammerGroupId]
,[IsDefaultCountryCodeCheckEnabled]
,[MetadataSyncLastTimeOfNeverExpiredDeletedObjects]
,[QuickFindRecordLimitEnabled]
,[YammerOAuthAccessTokenExpired]
,[UseSkypeProtocol]
,[DefaultCountryCode]
,[MetadataSyncTimestamp]
,[YammerNetworkPermalink]
,[YammerPostMethod]
,[UseReadForm]
,[EnableBingMapsIntegration]
,[MaximumActiveBusinessProcessFlowsAllowedPerEntity]
,[IncomingEmailExchangeEmailRetrievalBatchSize]
,[NotifyMailboxOwnerOfEmailServerLevelAlerts]
,[DefaultEmailServerProfileId]
,[GenerateAlertsForErrors]
,[EmailCorrelationEnabled]
,[IsAutoSaveEnabled]
,[DefaultEmailSettings]
,[EntityImageId]
,[EmailConnectionChannel]
,[BingMapsApiKey]
,[GenerateAlertsForInformation]
,[GenerateAlertsForWarnings]
,[AllowUsersSeeAppdownloadMessage]
,[SignupOutlookDownloadFWLink])
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]
,[DefaultRecurrenceEndRangeType]
,[InitialVersion]
,[HashFilterKeywords]
,[RecurrenceDefaultNumberOfOccurrences]
,[HashMinAddressCount]
,[AllowClientMessageBarAd]
,[ModifiedOnBehalfBy]
,[RequireApprovalForQueueEmail]
,[AllowEntityOnlyAudit]
,[RequireApprovalForUserEmail]
,[RecurrenceExpansionSynchCreateMax]
,[IsAuditEnabled]
,[GoalRollupExpiryTime]
,[BaseCurrencyPrecision]
,[FutureExpansionWindow]
,[FiscalPeriodFormatPeriod]
,[BaseISOCurrencyCode]
,[NextCustomObjectTypeCode]
,[ExpireSubscriptionsInDays]
,[OrgDbOrgSettings]
,[PastExpansionWindow]
,[EnableSmartMatching]
,[MaxRecordsForLookupFilters]
,[HashMaxCount]
,[ReportScriptErrors]
,[RecurrenceExpansionJobBatchSize]
,[GetStartedPaneContentEnabled]
,[SampleDataImportId]
,[PinpointLanguageCode]
,[CreatedOnBehalfBy]
,[HashDeltaSubjectCount]
,[GoalRollupFrequency]
,[FiscalYearFormatYear]
,[FiscalYearFormatPrefix]
,[BaseCurrencySymbol]
,[RecurrenceExpansionJobBatchInterval]
,[FiscalYearFormatSuffix]
,[IsUserAccessAuditEnabled]
,[UserAccessAuditingInterval]
,[AllowUserFormModePreference]
,[YammerGroupId]
,[IsDefaultCountryCodeCheckEnabled]
,[MetadataSyncLastTimeOfNeverExpiredDeletedObjects]
,[QuickFindRecordLimitEnabled]
,[YammerOAuthAccessTokenExpired]
,[UseSkypeProtocol]
,[DefaultCountryCode]
,[MetadataSyncTimestamp]
,[YammerNetworkPermalink]
,[YammerPostMethod]
,[UseReadForm]
,[EnableBingMapsIntegration]
,[MaximumActiveBusinessProcessFlowsAllowedPerEntity]
,[IncomingEmailExchangeEmailRetrievalBatchSize]
,[NotifyMailboxOwnerOfEmailServerLevelAlerts]
,[DefaultEmailServerProfileId]
,[GenerateAlertsForErrors]
,[EmailCorrelationEnabled]
,[IsAutoSaveEnabled]
,[DefaultEmailSettings]
,[EntityImageId]
,[EmailConnectionChannel]
,[BingMapsApiKey]
,[GenerateAlertsForInformation]
,[GenerateAlertsForWarnings]
,[AllowUsersSeeAppdownloadMessage]
,[SignupOutlookDownloadFWLink]
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
Comments
Post a Comment