ServiceBroker : Stored procedure working as stand-alone but not as trigger

We are trying to launch webservices asynchronously from SQL Server and Service Broker seemed the most interesting choice.

For Service Broker itself many different examples can be found online. I managed to have a working example in which I sent several parameters in a message on a queue and have the queue trigger automatically when a message arrives on it. The problem is that the code works fine when I just save the information to another table but it fails when I try to call a webservice. It then has the effect of stopping the queue.
It still works when I run the stored procedure by itself but not as a trigger on the queue. Does anyone have an idea of what I am doing wrong please?

My Stored Procedure for getting the message from the queue is as follows :

CREATE PROCEDURE usp_CommandLauncher
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @RecvReqDlgHandle AS UNIQUEIDENTIFIER;
    DECLARE @RecvReqMsg AS nvarchar(max);
    DECLARE @RecvReqMsgName AS sysname;
    DECLARE @xmlBody xml;
    DECLARE @MsgId int;
    DECLARE @ChannelType varchar(30);
    DECLARE @JobUrl varchar(50);
    DECLARE @LocalPath varchar(100);
    SET TEXTSIZE 2147483647;

    WHILE (1 = 1)
         BEGIN
             BEGIN TRANSACTION;
             WAITFOR(
             RECEIVE TOP (1) @RecvReqDlgHandle = conversation_handle,
                             @RecvReqMsg = message_body,
                             @RecvReqMsgName = message_type_name
                             FROM MessageQueue), TIMEOUT 5000;
             IF (@@ROWCOUNT = 0)
                 BEGIN
                     ROLLBACK;
                     BREAK;
                 END
             IF @RecvReqMsgName = N'AltovaWebRequests'
                 BEGIN
                    select @xmlBody = CAST(@RecvReqMsg as xml);
                    select @MsgId = @xmlBody.value('(//WebRequest/MsgId)[1]', 'int');
                    select @ChannelType = @xmlBody.value('(//WebRequest/ChannelType)[1]', 'varchar(30)');
                    select @FlowForceJobUrl = @xmlBody.value('(//WebRequest/JobUrl)[1]', 'varchar(50)');
                    select @LocalPath = @xmlBody.value('(//WebRequest/LocalPath)[1]', 'varchar(100)');
                    -- The following command always works fine
                    insert into ResultTable (MsgId, ChannelType, FlowForceJobUrl, LocalPath, [Conversation], DateTimeInserted) 
                        values (@MsgId, @ChannelType, @FlowForceJobUrl, @LocalPath, @RecvReqDlgHandle, getdate())
                    -- It stops functioning when I call the webservice
                    exec [spLaunchWebService_Test] @MsgId, @ChannelType, @JobUrl, @LocalPath
                 END
             ELSE
                 IF @RecvReqMsgName = N'<a class="linkclass" href="http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog">http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog</a>'
                     BEGIN
                         END CONVERSATION @RecvReqDlgHandle;
                     END
                 ELSE
                     IF @RecvReqMsgName = N'<a class="linkclass" href="http://schemas.microsoft.com/SQL/ServiceBroker/Error">http://schemas.microsoft.com/SQL/ServiceBroker/Error</a>'
                         BEGIN
                             END CONVERSATION @RecvReqDlgHandle;
                         END
             COMMIT TRANSACTION;
         END
END

The procedure spLaunchWebService_Test sets several parameters and calls a webservice
The Stored Procedure for calling a webservice can also be easily found online but I use this one :


ALTER PROCEDURE [dbo].[spHTTPRequest]
    @URI varchar(2000) = 'http://000.000.000.000/service/',     
    @methodName varchar(50) = 'Get',
    @requestBody varchar(8000) = '',
    @SoapAction varchar(255),
    @UserName nvarchar(100), -- DomainUserName or UserName
    @Password nvarchar(100),
    @responseText varchar(8000) output
as
SET NOCOUNT ON
IF @methodName = ''
BEGIN
    select FailPoint = 'Method Name must be set'
    return
END
set @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
    SELECT hResult = @hResult,
            description = @desc,
            MedthodName = @methodName
    goto destroy
    return
END
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT     hResult = @hResult,
            description = @desc,
            MedthodName = @methodName
      goto destroy
      return
END
-- set request headers
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = @hResult,
            description = @desc,
            MedthodName = @methodName
      goto destroy
      return
END
-- set soap action
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = @hResult,
            description = @desc,
            MedthodName = @methodName
      goto destroy
      return
END
declare @len int
set @len = len(@requestBody)
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT     hResult = @hResult,
            description = @desc,
            MedthodName = @methodName
      goto destroy
      return
END

-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF    @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = @hResult,
            description = @desc,
            MedthodName = @methodName
      goto destroy
      return
END
declare @statusText varchar(1000), @status varchar(1000)
-- Get status text
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status, @statusText, @methodName
-- Get response text
exec sp_OAGetProperty @objectID, 'responseText', @responseText out
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = @hResult,
            description = @desc,
            MedthodName = @methodName
      goto destroy
      return
END
destroy:
      exec sp_OADestroy @objectID
SET NOCOUNT OFF

Thanks and best regards,

Answer

Seems our issue is resolved ; Had to set “SET TRUSTWORTHY ON ” in the database were the servicebroker is running.