SQLTeam.com | Weblogs | Forums

SQL Service Broker

sql2014

#1
I have created a service broker that stores id's in the queue table. But the problem is when i want to get the id back in the stored procedure it's now formatted as xml. Because the service broker message is XML.

How can i just get the last Id each time because the trigger is fired after an update of a row.

Below my code =>

/****** Object:  Trigger [dba].[TriggerCall]    Script Date: 6/16/2015 2:55:57 PM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dba].[TriggerCall] ON  [dba].[CallID] FOR     UPDATE 
AS 

BEGIN
SET NOCOUNT ON;
DECLARE @MessageBody XML  
DECLARE @ID varchar(50)
-- Insert statements for trigger here

            --get relevant information from inserted/deleted and convert to xml message  
    SET @MessageBody = (SELECT Id FROM inserted  
    FOR XML AUTO)          

                If (@MessageBody IS NOT NULL)  
    BEGIN 

                DECLARE @Handle UNIQUEIDENTIFIER;   
                BEGIN DIALOG CONVERSATION @Handle   
                FROM SERVICE [TestServiceInitiator]   
                TO SERVICE 'TestServiceTarget'   
                ON CONTRACT [TestContract]   
                WITH ENCRYPTION = OFF;   
                SEND ON CONVERSATION @Handle   
                MESSAGE TYPE [TestMessage](@MessageBody);
    END

END



/****** Object:  StoredProcedure [dbo].[usp_GetCall]    Script Date: 6/16/2015 2:44:27 PM ******/

SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetCall]
AS
BEGIN 
DECLARE @message_type varchar(100) 
DECLARE @dialog uniqueidentifier, @message_body XML; 
Declare @Object as Int; 
Declare @URL as varchar(255)
Declare @ResponseText as Varchar(8000);
Declare @ID as Varchar(38);

WHILE (1 = 1) 
    BEGIN -- Receive the next available message from the queue 
    WAITFOR ( 
                RECEIVE TOP(1) @message_type = message_type_name,     
                @message_body = CAST(message_body AS XML),     
                @dialog = conversation_handle

    FROM dbo.TestQueue ), TIMEOUT 500    if (@@ROWCOUNT = 0 OR @message_body IS NULL) 

    BEGIN 

                BREAK 
    END 
          ELSE 
                BEGIN 

 INSERT INTO [dbo].[testtabel]
   ([id]
   ,[callid],
   [test])
 VALUES
   ('111', '111', @message_body)


                END
    END CONVERSATION @dialog 
    END
END