locked
Code will not Update with NewCode RRS feed

  • Question

  • I am re-aligning codes in MDM to match the systems that will use it. I have successfully used the staging leaf tables to do this already but there is one entity that just will not update. I started with update type 0 and then, adding the necessary values, tried update type 2. There are no errors, it just does not do anything. The end status is still 0. What could I be doing wrong?
    Friday, March 13, 2015 1:58 PM

Answers

  • I see. You are running at SQL Server 2012 SP2 RTM version.

    I don't have the exact version by my hand.

    But if you upgrade to latest 2012 SP2 CU which is CU5

    https://support.microsoft.com/en-us/kb/3037255

    The staging proc [stg].[udp_EntityName_Leaf] would have a section like low.

    --Process Insert all new error free records into MDS internal table.  
    UPDATE stgl  
    SET ImportStatus_ID = @StatusProcessing  
    FROM [stg].[CurrentFlag_Leaf] stgl  
    LEFT JOIN  mdm.[tbl_5_24_EN] en  
    ON      stgl.Code = en.Code   
        AND en.Status_ID = 1 -- Active  
        AND en.Version_ID = @Version_ID   
    WHERE stgl.Batch_ID = @Batch_ID   
        AND stgl.ImportType in (@IT_MergeOptimistic,@IT_Insert,@IT_MergeOverwrite)   
        AND stgl.ImportStatus_ID = @StatusDefault   
        AND en.Code IS NULL;  

    The possible code path could be:

    1. The entity is soft deleted. "en.Status_ID = 1 -- Active "

    2. The staging table Code does not match entity table code stgl.Code = en.Code

    3. The version you provided does not match the entity table version.

    Tuesday, March 17, 2015 7:14 PM

All replies

  • 1. What's the version of sql server?

    2. Can you check what's the ImportStatus_ID and ErrorCode of each row in the leaf table? The succeed row should be 1 as ImportStatus_ID and 0 as ErrorCode.

    3. Does the entity have the autogenerated code enabled?

    Friday, March 13, 2015 8:41 PM
  • SQL Server - 11.0.5058

    ImportStatus_ID - 0

    Errorcode - 0

    Create Code values automatically is enabled (but this did not make any difference for the other, successful, updates.)


    • Edited by Nick Samuel Tuesday, March 17, 2015 1:35 PM
    Tuesday, March 17, 2015 1:34 PM
  • I see. You are running at SQL Server 2012 SP2 RTM version.

    I don't have the exact version by my hand.

    But if you upgrade to latest 2012 SP2 CU which is CU5

    https://support.microsoft.com/en-us/kb/3037255

    The staging proc [stg].[udp_EntityName_Leaf] would have a section like low.

    --Process Insert all new error free records into MDS internal table.  
    UPDATE stgl  
    SET ImportStatus_ID = @StatusProcessing  
    FROM [stg].[CurrentFlag_Leaf] stgl  
    LEFT JOIN  mdm.[tbl_5_24_EN] en  
    ON      stgl.Code = en.Code   
        AND en.Status_ID = 1 -- Active  
        AND en.Version_ID = @Version_ID   
    WHERE stgl.Batch_ID = @Batch_ID   
        AND stgl.ImportType in (@IT_MergeOptimistic,@IT_Insert,@IT_MergeOverwrite)   
        AND stgl.ImportStatus_ID = @StatusDefault   
        AND en.Code IS NULL;  

    The possible code path could be:

    1. The entity is soft deleted. "en.Status_ID = 1 -- Active "

    2. The staging table Code does not match entity table code stgl.Code = en.Code

    3. The version you provided does not match the entity table version.

    Tuesday, March 17, 2015 7:14 PM