locked
Master Data Services Upgrade Database exception RRS feed

  • Question

  • I allowed a Windows Update to run on the server hosting SQL Server 2014.  When I tried to run the MDS Configuration Manager, I got "This database requires an upgrade. You cannot change system settings until the database is upgraded."  I clicked the Upgrade Database and got this exception:

    Microsoft.MasterDataServices.Configuration.ConfigurationException: Column names in each view or function must be unique. Column name 'VersionNumber' in view or function 'EntityHistory' is specified more than once. ---> System.Data.SqlClient.SqlException: Column names in each view or function must be unique. Column name 'VersionNumber' in view or function 'EntityHistory' is specified more than once.
       at Microsoft.MasterDataServices.Configuration.DatabaseUtilities.UpgradeMasterDataServicesDatabase(SqlConnection connection, String databaseName, Collection`1& businessRuleExclusionWarnings)
       at Microsoft.MasterDataServices.Configuration.Commands.UpdateMasterDataServicesDatabase.InternalProcessRecord()
       at Microsoft.MasterDataServices.Configuration.Commands.ConfigurationCmdlet.ProcessRecord()
       at System.Management.Automation.CommandProcessor.ProcessRecord()
       --- End of inner exception stack trace ---
       at Microsoft.MasterDataServices.Configuration.InstanceManager.Execute(Command command)
       at Microsoft.MasterDataServices.Configuration.InstanceManager.UpgradeMasterDataServicesDatabase(DatabaseServerInformation serverInformation, DatabaseInformation databaseInformation)
       at Microsoft.MasterDataServices.Configuration.UI.ServerManagement.ConfigurationAdministration.UpgradeDatabase(DatabaseInformation databaseInformation)
       at Microsoft.MasterDataServices.Configuration.UI.MdsDatabase.UpgradeDatabase()

    "EntityHistory" is an entity I created with MDS Configuration Manager before the Windows Update occurred.

    I have a series of SSIS packages that continue to load data into the database, so it appears to be ok.

    I have no idea what to do now. Please help.


    Tuesday, August 18, 2015 8:00 PM

Answers

  • By any chance, you have an attribute called "VersionNumber" in any of the entities?

    If so, the "VersionNumber" column name is used in an internal view, so you have to rename the attribute to something else and then try to upgrade again.

    Tuesday, August 18, 2015 9:19 PM
  • At this staging, there are 2 options.

    1. Downgrade the sql server to the lower version before the upgrade and then change the attribute name in the WebUI.

    2. Modify the attribute name manually.

      SELECT *
      FROM [yourdb].[mdm].[tblAttribute]
      WHERE Name = N'VersionNumber'
    
      UPDATE [yourdb].[mdm].[tblAttribute]
      SET Name = N'Version_Number'
      WHERE Name = N'VersionNumber'

    And then run the upgrade the database again.

    I suggest you backup the database before doing so.

    Wednesday, August 19, 2015 10:31 PM
  • Can you rename the stage table column as well and try again?
    EXEC sp_rename N'stg.EntityHistory_Leaf.VersionNumber', N'Version_Number', 'COLUMN';

    Monday, August 31, 2015 6:43 PM

All replies

  • By any chance, you have an attribute called "VersionNumber" in any of the entities?

    If so, the "VersionNumber" column name is used in an internal view, so you have to rename the attribute to something else and then try to upgrade again.

    Tuesday, August 18, 2015 9:19 PM
  • Yes, I do have a column named VersionNumber in my EntityHistory entity.  How can I rename the column when the MDS Configuration Manager won't let me get past the "This database requires an upgrade.  You cannot change system settings until the database is upgraded."?

    Can I change the column name through SSMS?  If so, which table/view do I edit?

    Wednesday, August 19, 2015 1:15 PM
  • Actually, I answered your question too quickly.  I am not sure if my entity has a column named VersionNumber.   How can I determine that?
    Wednesday, August 19, 2015 1:32 PM
  • So I did search of all the tables in my MDS database, and it turns out there is a column named VersionNumber.  It's in the leaf table created by MDS (slowly-changing dimension table).

    SELECT [ID],[ImportType]

          ,[ImportStatus_ID]
          ,[Batch_ID]
          ,[BatchTag]
          ,[ErrorCode]
          ,[Code]
          ,[Name]
          ,[NewCode]
          ,[EntityName]
          ,[EntityPrimaryKey]
          ,[PreviousValue]
          ,[CurrentValue]
          ,[StartDate]
          ,[EndDate]
          ,[Current]
          ,[CreatedBy]
          ,[CreatedDate]
          ,[Enabled]
          ,[VersionNumber]
      FROM [BOPCOFacilitiesMDS].[stg].[EntityHistory_Leaf]

    What can I do to resolve this conflict?

    Wednesday, August 19, 2015 2:19 PM
  • At this staging, there are 2 options.

    1. Downgrade the sql server to the lower version before the upgrade and then change the attribute name in the WebUI.

    2. Modify the attribute name manually.

      SELECT *
      FROM [yourdb].[mdm].[tblAttribute]
      WHERE Name = N'VersionNumber'
    
      UPDATE [yourdb].[mdm].[tblAttribute]
      SET Name = N'Version_Number'
      WHERE Name = N'VersionNumber'

    And then run the upgrade the database again.

    I suggest you backup the database before doing so.

    Wednesday, August 19, 2015 10:31 PM
  • I tried your renaming suggestion first.  Now I get "Invalid column name 'Version_Number'." after trying the database upgrade again.  Maybe this is an indication that something else is the problem?  Any ideas?
    Friday, August 28, 2015 7:07 PM
  • Can you rename the stage table column as well and try again?
    EXEC sp_rename N'stg.EntityHistory_Leaf.VersionNumber', N'Version_Number', 'COLUMN';

    Monday, August 31, 2015 6:43 PM