If I enable activation on my queue, as soon as a message arrives the queue
becomes disabled. I understand that this is usually due to an error
occurring in the activation SP. However, if I disable activation, let a
message arrive and then run the same SP manually using the same SQL login as
the queue uses, the SP completes successfully, the message is removed from
the queue and the queue is not disabled. What could be going on????

Here is the code of the SP:

create proc EurProcessNewReceiptMessages
as
begin -- SP

set nocount on
set xact_abort on

declare @MessageType sysname
declare @ConversationHandle uniqueidentifier
declare @MessageBody xml
declare @ErrorMessage varchar(8000)
declare @ReceiptID int
declare @TimeYearID int, @WeekNo int

--  This procedure will just sit in a loop processing messages from the
queue
--  until the queue is empty
while (1 = 1)
begin -- Check for new messages to process
begin transaction
 -- Receive the next available message
 waitfor (
  receive top(1) -- just handle one message at a time
  @MessageType = message_type_name,  --the type of message received
  @MessageBody = message_body,      -- the message contents
  @ConversationHandle = conversation_handle    -- the identifier of the
dialog this message was received on
  from RMIS_COMMS_Queue
 ), timeout 5000  -- if the queue is empty for five seconds, give up and go
away

 -- If we didn't get anything, the queue is empty so bail out
 if (@@rowcount = 0)
 begin -- No new message to process
  --rollback transaction
  commit transaction
  break
 end -- No new message to process
 else
 begin -- New message to process
  if (@MessageType =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
  begin -- Message is an End Dialog
   end conversation @ConversationHandle
  end -- Message is an End Dialog
  else if (@MessageType =
'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
  begin -- Message is an Error
   set @ErrorMessage = 'A message with type: ' + convert(varchar(8000),
@MessageType) + ' was found in Service Broker conversation: ' +
convert(varchar(8000), @ConversationHandle) + ' at: ' + convert(varchar(25),
getdate(), 101) + char(13)
   set @ErrorMessage = @ErrorMessage + 'Message contents: ' + char(13) +
convert(varchar(8000), @MessageBody)
   raiserror (@ErrorMessage , 19, 1) with log

   end conversation @ConversationHandle
  end -- Message is an Error
  else if (@MessageType = 'RMIS_Sales_Msg')
  begin -- Message is a receipt

   -- Check the incoming receipt number is not already in the database
   if not exists (select ph.* from pm_trans_header ph join
@MessageBody.nodes('/PMH') receipt(header) on ph.branch_id =
receipt.header.value('@BranchID', 'int') and ph.till_no =
receipt.header.value('@TillNo', 'int') and ph.receipt_no =
receipt.header.value('@ReceiptNo', 'varchar(15)'))
   begin -- Message is a new receipt

    select @TimeYearID = time_year_id, @WeekNo = current_week
    from global_param (nolock)
   -- LOTS OF CODE (THAT PROCESSES THE MESSAGE DATA) REMOVED HERE
   end -- Message is a new receipt
  end -- Message is a receipt

  commit transaction
 end -- New message to process
end -- Check for new messages to process
end -- SP
go

grant execute on EurProcessNewReceiptMessages to remcert
go

Here is a trace run on the server:

Broker:Connection          19 2006-12-21 11:01:57.857
Audit Broker Login          19 2006-12-21 11:01:59.280
Broker:Message Classify           19 2006-12-21 11:01:59.403
Broker:Conversation Group          19 2006-12-21 11:01:59.420
Broker:Conversation STARTED_INBOUND         19 2006-12-21 11:01:59.420
Broker:Conversation CONVERSING         19 2006-12-21 11:01:59.420
Broker:Remote Message Acknowledgement          19 2006-12-21 11:01:59.420
Broker:Conversation DISCONNECTED_INBOUND         19 2006-12-21 11:01:59.420
Broker:Remote Message Acknowledgement          19 2006-12-21 11:01:59.420
Broker:Activation          19 2006-12-21 11:01:59.420
Broker:Message Classify           20 2006-12-21 11:01:59.420
Broker:Remote Message Acknowledgement          18 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Activation          60 2006-12-21 11:01:59.420
Broker:Queue Disabled          20 2006-12-21 11:01:59.420
SQL:StmtRecompile waitfor (
  receive top(1) -- just handle one message at a time
  @MessageType = message_type_name,  --the type of message received
  @MessageBody = message_body,      -- the message contents
  @ConversationHandle = conversation_handle    -- the identifier of the
dialog this message was received on
  from RMIS_COMMS_Queue
 ), timeout 5000  -- if the queue is empty for five seconds, give up and go
away

 -- If we didn't get anything, the queue is empty so bail out