I have enabled Internal Activation on a queue in my system by specifying a stored procedure that is to handle messages as they hit the queue. This stored procedure posts a record to a table, then calls a stored procedure to process the data and post it to a set of tables. It then call another stored procedure that loops through a set of data to replicate the message back out to a set remote locations by calling another stored procedure to actually post the message to outbound queues. I have tested my stored procedure by executing it from Management Studio and it functions properly. However, when it is called by the queue only the initial posting of the message to a log table occurs. The store procedures do not seem to get called. Here is how the Internal ACtivation of the queue is set:
ALTER QUEUE Central_Queue
WITH
STATUS = ON,
ACTIVATION
(
STATUS=ON,
PROCEDURE_NAME = [RcvMsg@CO],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
)
I thought maybe the context was causing the call to fail, but I have tried everything I could think of and nothing seems to work. Any ideas where to look next?
P.S.: I looked in the Event Viewer and found the following message:
The activated proc [dbo].[RcvMsg@CO] running on queue Zarco.dbo.Central_Queue output the following:
‘SELECT failed because the following SET options have incorrect settings: ‘ARITHABORT’.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.’
What would cause this when it runs fine if I execute the stored proc from SQL Management Studio?
