MDS Error: “The status of the specified batch is not valid”
In Master Data Services (MDS), I received a strange error when I was calling a stored procedure (stg.upd_name_Leaf) to batch a staging table, which will load data from the staging tables into the appropriate MDS tables:
[Execute SQL Task] Error: Executing the query “declare @versionName nvarchar(50) set @versionNam…” failed with the following error: “MDSERR310029|The status of the specified batch is not valid.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Turns out, this error means that there is already a batch running with the BatchTag that I passed into the stored procedure (the BatchTag was “Owner”). This is explained in the Microsoft KB article MDS entity-based staging may fail when a duplicate Batch Tag value is used in SQL Server 2012. I had a few batch’s indefinitely stuck in the Running status which had the “Owner” batch tag that was due to a few bugs of mine as I was building out an SSIS package to load source data into the MDS tables.
To see what batch’s are in the running state, use “select * from mdm.tblStgBatch where batchtag=’Facility’ and status_id=3” (or from the MDS website by clicking Integration Management and then selecting the model to view the status). From the KB article, the solution is to pass in a different BatchTag (i.e. “Owner2”) or to stop the batch process. It says to stop the batch process, run the following SQL statement: “Exec [mdm].[udpStagingBatchQueueActivate]”. However, I have found that statement does not change the status of the running batches. Instead, you can change the status to completed via “Update mdm.tblStgBatch set status_id=7 where batchtag=’Facility’ and status_id=3”.
Thanks James, this saved a considerable amount of time in a pressure situation.
Great job. Good simple to following instructions.
Glad to help! I figured others would run into this.
Thanks, you saved my day 🙂
Mine too. I was thinking about updating the database table directly, but was nervous. Thanks for the confidence builder!
Hi James,
I tried your suggestion and was able to change the batch from Running to Completed. Then I updated the staging entity leaf records with a different batch tag. And, re-ran the staging process (EXEC [stg].[udp_Customer_Leaf] ‘VERSION_2’, 0, ‘TEST-02082013’). However, the new batch is also in running status and seems to be stuck even though I have a completely new batch tag.
Please let me know if you have any other thoughts.
Thanks & Regards,
Kamal
Hi James,
I too also facing the issue again after changed the NEW Batch tag name.
Please provide your valuable comments here. Thanks..
Best Regards,
Anbu J.
Hi James,
Am new to MDS, when am try to load data from staging_leaf Entity to MDS Entity by using Stored Proc, ImportStatus_ID=2 and ErrorCode=8 and ErrorCode =12.
what is wrong in my Process. And In stg.viw_EntityName_MemberErrorDetails Also am Not getting Error Details.
Please let me where i can do changes..
Thanks,
AkhilReddy.
can anyone please tell why the batch gets stuck in Running status? Is this environment issue or issue with MDS?
Hello,
Lately we have been receiving this error and there does not appear to be any results about that error code.
“MDSERR310052|An unknown error occurred when staging member attributes.”.
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set
correctly, or connection not established correctly.