Wednesday, June 28, 2017

Sitecore reporting database rebuild SQL script

As a part of the instructions of rebuilding the reporting database in Sitecore, there is a step where some SQL needs to be run manually on the database. When the process state is WaitReadyToReceiveData the contents of 7 database tables need to move from the primary DB to the secondary DB. The instructions state:
In the Rebuild Reporting Database page, when you see Waiting to receive to data status, copy the following marketing definition tables from the primary to the secondary reporting database:
  • CampaignActivityDefinitions
  • GoalDefinitions
  • OutcomeDefinitions
  • MarketingAssetDefinitions
  • Taxonomy_TaxonEntity
  • Taxonomy_TaxonEntityFieldDefinition
  • Taxonomy_TaxonEntityFieldValue
The SQL to copy this data is as follows:
INSERT INTO [reporting.secondary]..CampaignActivityDefinitions
SELECT * FROM [reporting]..CampaignActivityDefinitions

INSERT INTO [reporting.secondary]..GoalDefinitions
SELECT * FROM [reporting]..GoalDefinitions

INSERT INTO [reporting.secondary]..OutcomeDefinitions
SELECT * FROM [reporting]..OutcomeDefinitions

INSERT INTO [reporting.secondary]..MarketingAssetDefinitions
SELECT * FROM [reporting]..MarketingAssetDefinitions

INSERT INTO [reporting.secondary]..Taxonomy_TaxonEntity
SELECT * FROM [reporting]..Taxonomy_TaxonEntity

INSERT INTO [reporting.secondary]..Taxonomy_TaxonEntityFieldDefinition
SELECT * FROM [reporting]..Taxonomy_TaxonEntityFieldDefinition

INSERT INTO [reporting.secondary]..Taxonomy_TaxonEntityFieldValue
SELECT * FROM [reporting]..Taxonomy_TaxonEntityFieldValue
Ensure that the database names are updated to reflect that of your environment.

No comments:

Post a Comment