locked
Staging Process and Err300002 RRS feed

  • Question

  • Hello,  We have an interesting issue.  We inject data into MDS's staging table via the API.  We were inserting 17 Different people in one batch.  So an example would be we have a Phone, Address, Email entity which are all domain based attributes to a main Person Entity.  Of the 17 people we inserted 15 worked fine while the other 2 errored out with Err300002 - Member code is not valid.  Of the 2 people who failed there is no real differences to the data except a slightly different name, phone number.  Upon Deleting all the data for our tests and retrying it all worked.  This seems to be an intermittent error.

    Has anyone seen this before?  and if so is there a solution for it?

    Friday, October 14, 2011 6:41 PM

Answers

All replies

  • Hi Brian

    did you check the insertion order ?

    you should at first insert all the parent entities with their own code

    and then the children with domain based attribute code related to their parent's entity codes.

    for the 2 errors, are the 2 names containing some specific chars (e.g. ' or " or <") ?

    what is the member code for theses 2 errors ? and what is the entity member code of their parents ?

    you could also check mds trace log to get more error details

    (http://blogs.msdn.com/b/mds/archive/2010/02/09/trace-logging-in-sql-server-2008-r2-master-data-services.aspx)

    Regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

    Monday, October 17, 2011 12:41 PM
  • Thanks Xavier,  it is definitely not related to the order of the records being inserted.  We can clear out the system and retry and then they all work, or on another attempt different records would fail.  There are no special characters for the name or member code.  The member code for person is constructed with the following schema “SOURCENAME||SOURCEID”  so it’d be like “MySystem||1234”  and for Addresses it would be like “MySystem||1234||HomeAddress”

    The other difference in our system is that the staging stored procedure runs the Business rules when it processes the batch.  But this shouldn’t be a problem because the Business rules are ran after the data is committed. 

    Turning on the logs in MDS didn’t seem to work because we are using the API Directly from our service.  But the data does all get into the table problem.  I can manually set the mdm.tblstgBatch status ID to 1 and then it may work the second time.   The Member does get created but all the attributes are not populated.

     

    This is a very weird issue. 

     

    Monday, October 17, 2011 4:13 PM
  • weird indeed, yes

    which api method do you use ?

    maybe this could give you some info ? http://averbouch.biz/?p=8 and http://averbouch.biz/?p=9

    Regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Monday, October 17, 2011 4:53 PM
  • We are using the ModelMembersBulkMerge.  As an example the code is as follows.

     

     public bool ModelMembersBulkMerge(List<EntityMembers> membersToSave)
            {
                if ((membersToSave == null) || (membersToSave.Count == 0))
                {
                    return false; // nothing to save
                }
    
                var newMembersRequest = new ModelMembersBulkMergeRequest();
                ModelMembersBulkMergeResponse newMembersResponse;
                newMembersRequest.ModelMembers = membersToSave.ToCollection();
                try
                {
                    newMembersResponse = mdsClient.ModelMembersBulkMerge(newMembersRequest);
                }
                catch (Exception ex)
                {
                    logger.Error("Error talking to MDS db in UpsertMDSEntityMembersBulkMerge: thread is sleeping for 2s and will try one more time.", ex);
                    try
                    {
                        Thread.Sleep(2000);
                        newMembersResponse = mdsClient.ModelMembersBulkMerge(newMembersRequest);
                    }
                    catch (Exception ex2)
                    {
                        logger.Error("Second Attempt Failed for UpsertMDSEntityMembersBulkMerge, even after thread sleep of 2s.", ex2);
                        throw;
                    }
                }
                // do something with newMemberResponse.StgingBatches
    
                if (newMembersResponse.OperationResult != null && newMembersResponse.OperationResult.Errors.Count == 0)
                {
                    //Success
                    return true;
                }
                else
                {
                    logger.Error("MergeOperationResult error in Upsert", newMembersResponse.OperationResult);
                    return false;
                }
            }
    

    There is no errors returned in the api calls.  So the data is getting to the staging tables properly.  I believe its something internal to one of the following stored procedures

     - udpStagingProcess
     - udpStagingMemberSave
     - udpStagingMemberAttributeSave 

    It appears that when udpStagingMemberAttributeSave runs the data is not there from the udpStagingMemberSave.  

    We have in the past seen instances of where the batch process has started processing a batch before all the data is completely written, but I thought that was fixed.

    Monday, October 17, 2011 6:40 PM
  • hi Brian

    are you sure that char "||" will not interfere with SQL/MDS reserved chars ?



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Sunday, October 30, 2011 2:44 PM
  • These shouldn't be conflicting with any reserved characters, as the process seems to work the majority of the time.  There is just some weirdness that occures.  I have just gone through a full cycle of testing of a change to the staging processor "udpStagingProcessAllReadyToRun"  to prevent a bug that we've run into before of the batch being processed before the data has been completely written into the system via the API. 

     

    USE [MDS]
    GO
    /****** Object:  StoredProcedure [mdm].[udpStagingProcessAllReadyToRun]    Script Date: 10/31/2011 09:41:24 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [mdm].[udpStagingProcessAllReadyToRun]  
      
    AS  
    BEGIN  
        SET NOCOUNT ON;  
        /* WARNING --- YOU CANNOT INTRODUCE A TRY .. CATCH HERE WITHOUT   
         * SIGNIFICANTLY CHANGING THE BEHAVIOR OF UNDERLYING SPROCS. Contact [johnhall] with questions. */  
           
        DECLARE			    
         @User_ID			INT    
        ,@Batch_ID			INT    
        ,@LogFlag			INT = NULL  
        ,@Version_ID		INT
        ,@ValidateBatchAfterProcess BIT
        ,@RecordCount		INT =0
    	,@NewRecordCount   int =0  ;
    	  IF EXISTS(SELECT 1 FROM mdm.tblStgBatch WHERE Status_ID = 1) BEGIN    
    	    
    			-- Retrieve the logging system setting for transactions.  
    	  		SELECT  @LogFlag = SettingValue FROM mdm.tblSystemSetting WHERE SettingName = 'StagingTransactionLogging';  
    		    SELECT @ValidateBatchAfterProcess = SettingValue FROM mdm.tblSystemSetting WHERE SettingName = 'StagingValidateAfterBatch'
    		    
    		    IF @ValidateBatchAfterProcess IS NULL SET @ValidateBatchAfterProcess = 1;  -- Default to 1 if setting missing.
    	    
    		    
    		    WHILE EXISTS(SELECT 1 FROM mdm.tblStgBatch WHERE Status_ID = 1) BEGIN    
    				 
    			      SELECT TOP 1    
    				      @User_ID = EnterUserID,    
    				      @Batch_ID = ID,    
    				      @Version_ID = Version_ID     
    			      FROM mdm.tblStgBatch WHERE Status_ID = 1;    
       
    				  SELECT @RecordCount = ISNULL(mdm.tblStgBatch.TotalMemberCount,0) + ISNULL(mdm.tblStgBatch.TotalMemberAttributeCount,0) FROM mdm.tblStgBatch WHERE ID=@batch_ID
    				  WAITFOR DELAY '00:00:05'
    				  SELECT @NewRecordCount = ISNULL(mdm.tblStgBatch.TotalMemberCount,0) + ISNULL(mdm.tblStgBatch.TotalMemberAttributeCount,0) FROM mdm.tblStgBatch WHERE ID=@batch_ID
    					IF @RecordCount = @NewRecordCount
    						BEGIN
    							  --Update the Status for the batch    
    							  UPDATE mdm.tblStgBatch SET Status_ID = 3, LastRunStartDTM=GETUTCDATE(),LastRunStartUserID=@User_ID WHERE ID = @Batch_ID;    
    
    							  --Process Staging	    
    							  EXEC mdm.udpStagingProcess @User_ID, @Version_ID, 4, @LogFlag, @ValidateBatchAfterProcess, @Batch_ID;    
    			  
    							  --Update the Status for the batch    
    							  UPDATE mdm.tblStgBatch SET     
    								  Status_ID = 2,    
    								  LastRunEndDTM=GETUTCDATE(),    
    								  LastRunEndUserID=@User_ID,    
    								  ErrorMemberCount = (SELECT COUNT(ID) FROM mdm.tblStgMember WHERE Batch_ID = @Batch_ID AND Status_ID = 2),    
    								  ErrorMemberAttributeCount = (SELECT COUNT(ID) FROM mdm.tblStgMemberAttribute WHERE Batch_ID = @Batch_ID AND Status_ID = 2),    
    								  ErrorMemberRelationshipCount = (SELECT COUNT(ID) FROM mdm.tblStgRelationship WHERE Batch_ID = @Batch_ID AND Status_ID = 2)    
    							  WHERE     
    								  ID = @Batch_ID;    
          				 end
          			    ELSE
          					BEGIN
          					  WAITFOR DELAY '00:00:20'
          					END
          			    
    		    END; --while    
    			    
    		END; --if    
    		    
    		--Checked for Batches that need to be cleared		    
    		IF EXISTS(SELECT 1 FROM mdm.tblStgBatch WHERE Status_ID = 4) BEGIN    
    						    
    			  WHILE EXISTS(SELECT 1 FROM mdm.tblStgBatch WHERE Status_ID = 4) BEGIN    
    			    
    				    SELECT TOP 1    
    					    @User_ID = EnterUserID,    
    					    @Batch_ID = ID,    
    					    @Version_ID = Version_ID     
    				    FROM mdm.tblStgBatch WHERE Status_ID = 4;    
        												    
    				    --Clear the batch from staging				    
    				    DELETE FROM mdm.tblStgMember WHERE Batch_ID=@Batch_ID;    
    				    DELETE FROM mdm.tblStgMemberAttribute WHERE Batch_ID=@Batch_ID;    
    				    DELETE FROM mdm.tblStgRelationship WHERE Batch_ID=@Batch_ID;    
      
    				    --Update the Status for the batch    
    				    UPDATE mdm.tblStgBatch SET     
    					    Status_ID = 5,    
    					    LastClearedDTM=GETUTCDATE(),    
    					    LastClearedUserID=@User_ID    
    				    WHERE     
    					    ID = @Batch_ID;    
    					    
    			  END; --while    
    			    
    		END; --if    
      
    	  
    END
    
    

    The changes were pretty basic.  The conceptual change is this

      1)  Query the system for the batch that is going to be processed  and total up the TotalMembers and Total Attributes

      2)  Wait a defined period of time

    3)  Query the same batch again and if the numbers have changed then sleep for another defined period of time

    4)  Do it all over again.

     

    This change has now prevented a whole bunch of errors that we were seeing while using the api.  We were seeing things like the Status ID and the Error code for Members and Attributes from never getting set.  It has also prevented the "Unknown Error has occured.  ERR2100055"  

    I don't like changing code MDS code but this seemed to be necessary. 

     

    Brian.
     

    Monday, October 31, 2011 4:45 PM