CODEWITHSUNDEEP
sql-serversql-server-2008service-broker
user351711
user351711

Reputation: 3301

SQL Server Service Broker POISON_MESSAGE_HANDLING(STATUS = ON)

We are hosting one of our solutions which was originally built on SQL Server 2008 R2 and this instance is hosted on a SQL Server 2008 instance (not R2). The database created fine but for some reason the service broker queues were created with:

POISON_MESSAGE_HANDLING(STATUS = OFF)

I have tried setting this to on but with no luck, we have always declared the queue like this:

CREATE QUEUE QueueName WITH STATUS=ON, ACTIVATION 
(STATUS = ON, MAX_QUEUE_READERS = 1, 
PROCEDURE_NAME = QueueProcedureName,   EXECUTE AS OWNER);

Is there a way to create the queue as about with the defaults of R2?

EDIT - More Info:

This is the error message which makes no sense as works fine on 2008 R2. GO ALTER QUEUE [Store].[UpdateStoredPublishingSegmentUsersSendQueue] WITH POISON_MESSAGE_HANDLING(STATUS = ON);

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'POISON_MESSAGE_HANDLING'.

Upvotes: 0

Views: 1666

Answers (2)

Vijay
Vijay

Reputation: 124

This is an issue with the version of sql server. The POISON-MESSAGE_HANDLING is not supported in version less than 2008 R2. Hope this helps!

Upvotes: 1

Stefan Anghel
Stefan Anghel

Reputation: 194

DISCLAIMER: I haven't tried the following commands, as I am running on 2005 which doesn't support the POISON_MESSAGE_HANDLING option.

Have you tried the ALTER QUEUE command after executing the CREATE?

ALTER QUEUE <queue name> WITH 
    POISON_MESSAGE_HANDLING ( STATUS = ON )

In alternative try modifying your CREATE command like this:

CREATE QUEUE <queue name> WITH 
    STATUS=ON, 
    ACTIVATION (
        STATUS = ON, 
        MAX_QUEUE_READERS = 1, 
        PROCEDURE_NAME = <activated sproc name>,
        EXECUTE AS OWNER
    ),
    POISON_MESSAGE_HANDLING ( STATUS = ON );

Upvotes: 0

Related Questions