Monday, February 27, 2012 5:04 AM
I am bulk load data from Legacy to MDS using staging tables. After load data in tblStgBatch, tblStgMembers and tblStgMemberAttributes. I am trigger the staging process using the following stored procedure calls.
--Create staging batch EXEC MDS.mdm.udpStagingBatchSave @UserID = @User_ID, @VersionID = @Version_ID, @Name = @BatchName, @StatusID = 1, @ReturnID = @Batch_ID OUTPUT INSERT INTO MDS.mdm.tblStgMemberAttribute (Batch_ID, UserName, ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) SELECT @Batch_ID, @pUserName, @pModelName, EntityName, 1, MemberCode, AttributeName, AttributeValue FROM @tblAttribute -- WHERE MemberCode IN (SELECT @pCountryName+'-'+Code FROM @tblMember) SET @AttRowCount += ROWCOUNT_BIG() --Update staging batch EXEC MDS.mdm.udpStagingBatchSave @UserID = @User_ID, @VersionID = @Version_ID, @BatchID = @Batch_ID, @StatusID = 1, @TotalMemberCount = @MbrRowCount, @TotalMemberAttributeCount = @AttRowCount, @TotalMemberRelationshipCount = 0 --Invoke staging EXEC MDS.mdm.udpStagingProcess @User_ID = @User_ID, @Version_ID = @Version_ID, @StagingType_ID = 4, @LogFlag = 1, @DoValidate = 0, @Batch_ID = @Batch_ID
Even after a successful load to MDS the status_id = 1 and not set to "2" in tblStgBatch table?
Only thing I am doing different is that I have two separate Batches to load Members and Attributes i.e. I have to records in tblStgBatch one for members and other for Attributes.
Does the status in tblStgBatch is dependent on tblStgMembers and tblStgMemberAttributes status or they are independent?
Tuesday, February 28, 2012 4:02 AM
Error may occur during staging.It updates each row in staging table as status_ID coloumn
1- Staging is successful
In case its 2 chech for error code and it can help you to troubleshoot.
If you are getting 1 than there is no issue.Check if you see any information/error messages in Web App of MDS
Wednesday, February 29, 2012 5:19 AM
Yes in tblStgMember and tblStgMemberAttribute Status_id =1 is successful and 2 is failure. But in tblStgBatch Status_id = 1 (Queued to run) where as Status_id = 2 (Not running i.e. stopped after running)
My issue is that my Status_id in tblStgBatch never changes to Status_id =2 even if there are no failures in tblStgMember and tblStgMemberAttribute.
Wednesday, February 29, 2012 9:29 AM
Oops Missed!My Bad!
So your batch is not changing from queued to running to Not Running state.
1.Please check if you servicebroker is enabled/running or you can see any error in eventviewer from service broker.
2.you can manually trigger it by executing the procedure udpStagingBatchQueueActivate
- Marked As Answer by Challen FuModerator Wednesday, March 07, 2012 10:05 AM
Tuesday, March 27, 2012 1:02 AMRun the following code this will kick of the staging process instantly
SELECT @versionid = mv.ID FROM mdm.tblModelVersion mv INNER JOIN mdm.tblModel m ON mv.Model_ID = m.ID WHERE m.Name = @ModelName AND mv.Name = @ModelVersionName EXEC mdm.udpStagingSweep @UserID, @versionid, 1