Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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

Comments

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

Loading comments...