Question Locked

Internal Activation of Queue on Stored Procedure

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

3 replies

by michaelbuck

I am answering my own question for those who hit the same wall as I did. The fix for this only requires that the database have a compatibility level of 90, not 80. Change this and suddenly everything works as expected....wow....
Look at the properties of the database, select options and make sure the compatibility level is set to 90(SQL Server 2005)

by tony hopkinson

question, but it has been the answer to several others.

For those not aware if you use the tool to upgrade your database to SQL 2005, don't forget to set the compatibility mode. :(

This discussion is closed to new replies.