locked
MDS Staged Batch status remains as QUEUED TO RUN RRS feed

  • Question

  • I am facing issue when trying to import into staging. The status is always queued to run and never changes. The root cause is obviously SSB but coudn't pin-point it. Initial investigation shows, the message is successfully queued into "[microsoft/mdm/queue/stagingbatch]"; I guess this message must be read by stored procedure "[udpStagingBatchQueueActivate]". Now since the message is queued successfully I guess the issue is "udpStagingBatchQueueActivate" is not invoked. Any thought on how this Stored Procedure is invoked.

    Note - The resolution discussed in another thread for similar issue as described in link" http://msdn.microsoft.com/en-us/library/ff486994(SQL.105).aspx#CommunityContent " didn’t help since I can't see any permission issue in event log.

    Friday, November 4, 2011 8:52 PM

Answers

  • Thanks briberry !

    In my case User permission are fine and all are queue are up and running.

    you are perfectly right , the reason for this issue is restoring backup . On restoring DB SSB will disabled and this will cause the attempt for activation of MDM internal queue getting failed.one thing i found displeasing is when i enable the SSB again using script , it doesnt activate this queue "[microsoft/mdm/queue/stagingbatch]" may be MSFT should have some failesafe . Anyways i executed the SP "[udpStagingBatchQueueActivate]" manually once and staging process is working fine since then .

    • Marked as answer by AnupKumar Wednesday, November 9, 2011 7:19 AM
    Wednesday, November 9, 2011 7:19 AM

All replies

  • I wonder if maybe you restored this MDS database from a backup.  I have found that when restoring an MDS hub, I must run the following script to clean things up:

    -- Change MDS to name of your Hub Database
    ALTER DATABASE MDS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE MDS SET ENABLE_BROKER
    -- After a restore of an MDS database, set the owner to the original owner
    ALTER AUTHORIZATION ON DATABASE::MDS TO [mds_dlp_login];
    -- Set trustworthy on for restored DBs
    ALTER DATABASE MDS SET TRUSTWORTHY ON;
    ALTER DATABASE MDS SET MULTI_USER


    Two keys here, I think: the owner of the database is restored to the SQL Login mds_dlp_login AND that database is set  to TRUSTWORTHY. 

    7 of 8 Queues have activation turned on (all but ExternalActions) - the means that a seperate process is spawned under the context of a new user. Yes, the message is passed to Stored Procedure named in the Queue definition. Here is the one you are referring to:

    ALTER QUEUE [mdm].[microsoft/mdm/queue/stagingbatch] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [mdm].[udpStagingBatchQueueActivate] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'mds_ssb_user'  ), POISON_MESSAGE_HANDLING (STATUS = ON) 

    I believe if you check the SQL Server log you will see that mds_ssb_user does not have the required permissions needed, possibly it is trying to invoke a stored procedure in another SQL Server database (i.e. Master).

    Anyway, try the script, and see if that fixes the permission problem.

     

     


    Brian Berry
    Wednesday, November 9, 2011 3:10 AM
  • Thanks briberry !

    In my case User permission are fine and all are queue are up and running.

    you are perfectly right , the reason for this issue is restoring backup . On restoring DB SSB will disabled and this will cause the attempt for activation of MDM internal queue getting failed.one thing i found displeasing is when i enable the SSB again using script , it doesnt activate this queue "[microsoft/mdm/queue/stagingbatch]" may be MSFT should have some failesafe . Anyways i executed the SP "[udpStagingBatchQueueActivate]" manually once and staging process is working fine since then .

    • Marked as answer by AnupKumar Wednesday, November 9, 2011 7:19 AM
    Wednesday, November 9, 2011 7:19 AM