• Post Categories

  • Browse Blogs

  • Blog Stats

    • 564,506 hits
  • Syndications

    SQLServerPedia Contributor
  • Advertisements

Master Data Services Error: Resolving from a long-running stucked Staging Batch with Status “Queued to Clear” or “Not Running”

In some ocassions,  you may run into a runaway, long-running or stucked staging batch with Master Data Services in SQL Server 2008 R2. The issue happens most frequently when a fairly large batch of rows are being processed for update or deletion.

The issue has to do with Service Broker, either due to a timeout or notifications not being received or received incomplete.  You can confirm the issue when you query the queue [mdm].[microsoft/mdm/queue/stagingbatch]. You will see one or more messages in this queue. After searching through a couple of forum threads I found the solution to be fairly simple: rollback transactions an re-enable Service Broker. The steps are as follows:

  1. Set the MDS database in single user mode and rollback uncommitted transactions.
    ALTER DATABASE [MDS] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
  2. Re-enable Service Broker on the MDS database.
    ALTER DATABASE [MDS] SET  ENABLE_BROKER WITH NO_WAIT;
  3. Set the MDS database back in to “regular” multi-user mode.
    ALTER DATABASE [MDS] SET  MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the MDS database is brought back online, all queued messages should be cleared out and MDS database will be able to communicate normally with Service Broker.

You can check Books on Line for more information on the difference between “NO WAIT” and “WITH ROLLBACK IMMEDIATE” options when issuing the ALTER DATABASE command here. Pinal Dave has a good post about the difference between these two options as well here.

Hope this post has helped you. Your feedback is greatly appreciated.

Advertisements

SQL Server 2012 Master Data Services Error: The required .svc handler mappings are not installed in IIS.

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) has a confirmed issue with Master Data Services configuration. The following error is raised by the Master Data Services Configuration Manager when launched:

Before creating an MDS web application, you must correct the following errors:
Internet Information Services (IIS) is not configured on this server.
The required .svc handler mappings are not installed in IIS. For more information, see http://go.microsoft.com/fwlink/?LinkId=226284.
For web application requirements, see http://go.microsoft.com/fwlink/?LinkId=215355.
This issue has been communicated to Microsoft via Connect ID: 701993 and is documented in the Technet Article “Troubleshoot Installation and Configuration Issues (Master Data Services in SQL Server 2012)”
The Workaround
After several days trying to figure out the issue, today I received notice from Reagan Templin (LinkedIn | Twitter), technical writer for Microsoft, that the MDS Team has identified the issue. This issue will NOT be fixed in the current RCO but will be fixed in the RTM version. The error can be replicated under the following conditions:
  1. Configuring Master Data Services on a server that is not joined to a Windows domain
  2. Running Master Data Service Configuration Manager with a local account (non-domain user) on a server joined to a Windows domain.

The workaround is simply to join the server to a Windows domain and launch Master Data Services Configuration Manager with a domain account that has administrator privileges. In my case,  after I joined the server to a domain I was able to complete by MDS configuration.

Many thanks to Reagan Templin for her above and beyond customer service. She followed up on this issue directly with the MDS Team after I posted a message on Twitter about the issue I was having and kept me posted with updates towards resolving the issue.

MDS Error: MDMMemberStatus does not de-activate members through the StgMemberAttribute table

Master Data Services provides a way of de-activating entity members using the StgMemberAttribute table as described in Books On Line http://msdn.microsoft.com/en-us/library/ee633772.aspx

This is a somewhat straightforward approach and requires for you to populate the StgMemberAttribute table with AttributeCode (actual code of the member in MDS), AttributeName as “MDMMemberStatus” and AttributeValue as “De-Activated”.

In a recent MDS implementation, I came across an error when I was trying to de-activate all members from an entity. I followed the same steps I usually follow but the members were still not being deactivated. I noticed that after the rows were processed, a batch number was assigned but the Status_ID column in the StgMemberAttribute remained unchanged with the default value of 0 (ready for staging). The ErrorCode column also remained unchanged.

After several hours of research I bumped into a fix introduced in the MDS June 2010 Cumulative Update. The fix found under the Import and Export section states:

The staging process supports the deletion of members from an entity that supplies domain-based attribute values for another entity.

Bingo! The members I was trying to de-activate were part of an entity being used as the source of a domain-based attribute of another entity. I had confirmed that no members were being referenced and I even deleted this referencing domain-based attribute while trying to de-activate the members.

After applying the MDS June 2010 Cumulative Update I was able to delete the members of this entity. 

As of the date of this post, Service Pack 1 and two additional Cumulative Updates have been released for MDS. You can check what was fixed in these updates and how to install them at the MDS team blog site: http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

%d bloggers like this: