title: ServiceBroker : Stored procedure working as stand-alone but not as trigger
body: <p>We are trying to launch webservices asynchronously from SQL Server and Service Broker seemed the most interesting choice.</p>
  <p>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.<br />
  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?</p>
  <p>My Stored Procedure for getting the message from the queue is as follows :</p>
  <pre><code>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'&lt;a class=&quot;linkclass&quot; href=&quot;http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog&quot;&gt;http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog&lt;/a&gt;'
                       BEGIN
                           END CONVERSATION @RecvReqDlgHandle;
                       END
                   ELSE
                       IF @RecvReqMsgName = N'&lt;a class=&quot;linkclass&quot; href=&quot;http://schemas.microsoft.com/SQL/ServiceBroker/Error&quot;&gt;http://schemas.microsoft.com/SQL/ServiceBroker/Error&lt;/a&gt;'
                           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), -- Domain\UserName 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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; 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
  </code></pre>
  <p>Thanks and best regards,</p>