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″.

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in MDS/MDM, SQLServerPedia Syndication. Bookmark the permalink.

7 Responses to MDS Error: “The status of the specified batch is not valid”

  1. Blair says:

    Thanks James, this saved a considerable amount of time in a pressure situation.

    Great job. Good simple to following instructions.

  2. Claus says:

    Thanks, you saved my day :-)

    • Eric says:

      Mine too. I was thinking about updating the database table directly, but was nervous. Thanks for the confidence builder!

  3. Kamal Shaikh says:

    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

    • Anbu says:

      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.

  4. Akhil says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>