Asynchronous Trigger

I am writing an asynchronous trigger and all looks well. There are no errors although the trigger is firing the stored procedure broker service queue is not. any ideas?

/* -------------------------------------------------------------------------
            This procedure will run asynchronously and execute our task
------------------------------------------------------------------------- */

CREATE PROCEDURE LeadTrax_CBBain_AsynchronousCall
AS
-- This procedure will get triggered automatically
-- when a message arrives at the queue.
-- Let's retrieve any messages sent to us here:
DECLARE      @XML XML,
            @MessageBody VARBINARY(MAX),
            @MessageTypeName SYSNAME,
            @LeadID uniqueidentifier;
DECLARE @Queue TABLE (
            MessageBody VARBINARY(MAX),
            MessageTypeName SYSNAME);
WHILE (1 = 1)
BEGIN
      WAITFOR (
            RECEIVE message_body, message_type_name
            FROM LeadTrax_CBBainProfitPowerDataMergeQueue
            INTO @Queue
      ), TIMEOUT 5000;
      -- If no messages exist, then break out of the loop:
      IF NOT EXISTS(SELECT * FROM @Queue) BREAK;
      DECLARE c_ProcessQueue CURSOR FAST_FORWARD
            FOR SELECT * FROM @Queue;
      OPEN c_ProcessQueue;
      FETCH NEXT FROM c_ProcessQueue
            INTO @MessageBody, @MessageTypeName;
      WHILE @@FETCH_STATUS = 0
      BEGIN
            -- Let's only deal with messages of Message Type
            IF @MessageTypeName = 'LeadTrax_CBBainProfitPowerDataMergeMessage'
            BEGIN
                  SET @XML = CAST(@MessageBody AS XML);
                  -- Now let's get our data from the XML records into the
                  

                  DECLARE @tmpT Table(LeadID uniqueidentifier);
                  insert into @tmpT (LeadID)
                  SELECT
                  tbl.rows.value('@LeadID', 'uniqueidentifier') as LeadID
                  FROM @XML.nodes('/inserted') tbl(rows);
                  --FROM @XML.nodes('/row') AS Table1(Column1)


                  DECLARE @LeadID_ToSend as uniqueidentifier
                  SELECT @LeadID_ToSend = LeadID
                  FROM @tmpT


Print '@LeadID_ToSend ' + CAST(@LeadID_ToSend as nvarchar(50))


                  EXEC AgentResourceCenter4_clientuat.dbo.LeadTrax_CBBain_SyncData @LeadID_ToSend, Default



            END
            FETCH NEXT FROM c_ProcessQueue
                  INTO @MessageBody, @MessageTypeName;
      END
      CLOSE c_ProcessQueue;
      DEALLOCATE c_ProcessQueue;
      -- Purge the temporary in-proc table:
      DELETE FROM @Queue;
END


GO






/* -------------------------------------------------------------------------
            CREATE THE SERVICE BROKER MESSAGE TYPE
------------------------------------------------------------------------- */
CREATE MESSAGE TYPE LeadTrax_CBBainProfitPowerDataMergeMessage
      VALIDATION = WELL_FORMED_XML;
GO


/* -------------------------------------------------------------------------
            CREATE THE SERVICE BROKER CONTRACT
------------------------------------------------------------------------- */
CREATE CONTRACT LeadTrax_CBBainProfitPowerDataMergeContract
      (LeadTrax_CBBainProfitPowerDataMergeMessage SENT BY ANY);
GO



/* -------------------------------------------------------------------------
            CREATE THE SERVICE BROKER QUEUE
------------------------------------------------------------------------- */

CREATE QUEUE [LeadTrax_CBBainProfitPowerDataMergeQueue]
      WITH STATUS = ON,
      RETENTION = OFF,
      ACTIVATION (
            STATUS = ON,
            PROCEDURE_NAME = AgentResourceCenter4_clientuat.dbo.LeadTrax_CBBain_AsynchronousCall,
            MAX_QUEUE_READERS = 100,
            EXECUTE AS OWNER)
      ON [DEFAULT];

/*
MAX_QUEUE_READERS = max_readers
Specifies the maximum number of instances of the activation stored procedure
that the queue starts at the same time. The value of max_readers must be a
number between 0 and 32767.
*/

GO




/* -------------------------------------------------------------------------
            CREATE THE SERVICE BROKER SERVICE
------------------------------------------------------------------------- */
CREATE SERVICE LeadTrax_CBBainProfitPowerDataMergeService
      ON QUEUE LeadTrax_CBBainProfitPowerDataMergeQueue
      (LeadTrax_CBBainProfitPowerDataMergeContract);
GO


/* -------------------------------------------------------------------------
            CREATE THE TRIGGER
------------------------------------------------------------------------- */

-- We need one on the Leads and the LeadsToUsers table

CREATE TRIGGER LeadTrax_Leads_CBBainProfitPowerDataMerge ON LeadTrax_Leads FOR UPDATE
AS
-- We don't want the following T-SQL to affect the
-- UPDATE, so let's place it in a different
-- transaction; if the following code raises an error,
-- only it will be rolled back:
SET XACT_ABORT OFF;
-- Send an asynchronous message to a Service Broker queue
-- with the affected records:
-- TO MAKE THIS A TRUELY ASYNCHRONOUS TRIGGER, WE MUST
-- PERFORM AS LITTLE WORK AS POSSIBLE HERE AND WITHOUT
-- TYING UP THE RESOURCES OF THE TRIGGER.
DECLARE @XML XML;
IF EXISTS(SELECT * FROM inserted)
BEGIN
      BEGIN TRAN;
      BEGIN TRY
            -- Save the records as XML
            SELECT @XML = (SELECT * FROM inserted FOR XML AUTO);
            -- Send the XML records to the Service Broker queue:
            DECLARE @DialogHandle UNIQUEIDENTIFIER,
                  @ConversationID UNIQUEIDENTIFIER;
            /*
                  The target Service Broker service is the same
                  service as the initiating service; however, you
                  can set up this type of trigger to send messages
                  to a remote server or another database.
            */
            BEGIN DIALOG CONVERSATION @DialogHandle
                  FROM SERVICE LeadTrax_CBBainProfitPowerDataMergeService
                  TO SERVICE 'LeadTrax_CBBainProfitPowerDataMergeService'
                  ON CONTRACT LeadTrax_CBBainProfitPowerDataMergeContract;
            SEND ON CONVERSATION @DialogHandle
                  MESSAGE TYPE LeadTrax_CBBainProfitPowerDataMergeMessage
                  (@XML);
            -- Let's detect an error state for this dialog
            -- and rollback the entire transaction if one is
            -- detected:
            IF EXISTS(SELECT * FROM sys.conversation_endpoints
                  WHERE conversation_handle = @DialogHandle
                  AND state = 'ER')
                  RAISERROR('Dialog in error state.', 18, 127);
            ELSE
            BEGIN
                  -- We don't care about a reply from the target, so let's
                  -- end the conversation here:
                  END CONVERSATION @DialogHandle;
                  COMMIT TRAN;
            END
      END TRY
      BEGIN CATCH
            ROLLBACK TRAN;
            DECLARE @ERRMSG VARCHAR(MAX);
            SET @ERRMSG = ERROR_MESSAGE();
            RAISERROR(@ERRMSG, 18, 127);
      END CATCH;
END
GO













 
 
CREATE TRIGGER LeadTrax_LeadsToUsers_CBBainProfitPowerDataMerge ON LeadTrax_LeadsToUsers FOR UPDATE

AS

BEGIN

SET NOCOUNT ON;

      DECLARE @MessageBody XML;
      set @MessageBody = (SELECT LeadID FROM Inserted FOR XML RAW, TYPE);

      DECLARE @Handle UNIQUEIDENTIFIER;

      BEGIN DIALOG CONVERSATION @Handle

      FROM SERVICE [LeadTrax_CBBainProfitPowerDataMergeService]

      TO SERVICE 'LeadTrax_CBBainProfitPowerDataMergeService'

      ON CONTRACT [LeadTrax_CBBainProfitPowerDataMergeContract]

      WITH ENCRYPTION = OFF;

      SEND ON CONVERSATION @Handle

      MESSAGE TYPE [LeadTrax_CBBainProfitPowerDataMergeMessage](@MessageBody);


Print 'RAN TRIGGER - LeadTrax_LeadsToUsers_CBBainProfitPowerDataMerge'


END

GO
 
 

Thanks
jason





Start Free Trial