SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...