none
Restore-SBMessageContainer: "Column names in each table must be unique. Column name 'MessageCountDetails' in table 'EntityLookupTable' is specified more than once. RRS feed

  • Question

  • Hi,

    I was trying to restore workflow manager farm and while in the step of Restoring Message Container I started getting following error:

    TerminatingError(Restore-SBMessageContainer): "Column names in each table must be unique. Column name 'MessageCountDetails' in table 'EntityLookupTable' is specified more than once."
    Column names in each table must be unique. Column name 'MessageCountDetails' in table 'EntityLookupTable' is specified more than once.

    AND

    Restore-SBGateway): "Column names in each table must be unique. Column name 'MessageCountDetails' in table 'EntityLookupTable' is specified more than once."
    Column names in each table must be unique. Column name 'MessageCountDetails' in table 'EntityLookupTable' is specified more than once.


    When I checked EntityLookupTable, I found there is only one MessageCountDetails column:

    I was able to restore workflow manager in Feb'2020 without any problem but this time I'm getting above exception while Executing Restore-SBMessageContainer and Restore-SBGateway commands.

    Does anyone know how to resolve this issue, it's urgent need HELP.


    Best Regards,
    Brij K
    http://bloggerbrij.blogspot.co.uk/

    Monday, May 25, 2020 1:13 PM

All replies

  • I've solved this problem by modifying CommonSqlMessagingStoreUpgrade.sql script in "C:\Program Files\Service Bus\1.1" folder to include check if Column exists then continue.

    I've added following lines in this script:

    Line 171: AND COL_LENGTH('dbo.EntityLookupTable', 'MessageCountDetails') IS NULL  --ADDED DURING UPGRADE
    Line 182: AND NOT EXISTS(SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name = 'GetExpiredMessagesInputTableType') --ADDED DURING UPGRADE
    Line 223: AND COL_LENGTH('dbo.EntityLookupTable', 'AutoDeleteOnIdleDurationInSeconds') IS NULL --ADDED DURING UPGRADE
    Line 233: AND NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='NCIX_ExpiryTime' AND object_id = OBJECT_ID('dbo.EntityLookupTable')) --ADDED DURING UPGRADE
    Line 321: AND COL_LENGTH('dbo.EntityLookupTable', 'IsScaledReceive') IS NULL --ADDED DURING UPGRADE
    Line 332: AND COL_LENGTH('dbo.EntityLockTable', 'MarkedOffline') IS NULL --ADDED DURING UPGRADE
    Line 358: AND NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'EntityCreditUsageTable') --ADDED DURING UPGRADE
    

    This problem arises because of Feb'2020 upgrade where I've executed Restore-SBGateway before running Restore-SBMessageContainer command as stated in Workflow Manager DR document. But as SbGatewayDB has reference to Live SbMessageContainerDB in table so it has upgraded live database, ignoring Connection String provided during Restore-SBFarm command execution. 

    I guess this is bug in Workflow Manager Restore process, OR it's created just for DR purpose only and not for migration/upgrade with assumption existing box is not operational. 

    Any comment from Microsoft Team.



    Best Regards,
    Brij K
    http://bloggerbrij.blogspot.co.uk/

    Wednesday, June 10, 2020 9:36 AM