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