CODEWITHSUNDEEP
sql-serverservice-broker
murphytalk
murphytalk

Reputation: 1297

Service Broker : keep messages that could not be handled by an external program in the queue

I have an external program which will call a stored procedure to wait for message on a queue and then process it. The problem is sometimes the message read from the queue might not be handled properly, when it happens I would like to keep the message stay in the queue until it can be processed.

It looks unless the queue is created by specifying RETENTION, message will be always be removed from the queue upon a successful WAITFOR, unless the transaction is rolled back. But as you can see I won't be able to know if the message is valid until the stored procedure return the message to the caller,which is the java program. I am wondering is it possible to break the "begin transaction" and "commit" or "rollback" to two stored procedures: call the stored procedure which begins transaction and waitfor message first; when it returns with the message, try to process the message in the java code;if the message is processed successfully, call the 2nd stored procedure to commit the transaction, or call another stored procedure to rollback it and put the message back to the queue.

My concern is , how to specify the right transaction to commit or rollback since they are not called in a same stored procedure?

Is there any other good practice to handle this situation? I have another alternative idea is to create an exception queue, let the java code put failed message to the exception queue.

Any comments will be appreciated!

Upvotes: 0

Views: 1320

Answers (1)

Jānis
Jānis

Reputation: 2266

you don't call stored procedure to wait for messages, instead just query the queue. 

WAITFOR (RECEIVE conversation_handle,message_type_name,service_contract_name,convert(xml, message_body) FROM [dbo].[MyQueue])

When new message arrives, keep transaction open, process message- if message has unexpected error, then rollback transaction. If expected error- end conversation with error.

Actually i think good idea is to use external activator (msdn, name of download: "Microsoft SQL Server 2008 R2 Service Broker External Activator") You can also make something similar like is already made in .net (ServiceBrokerInterface)

Upvotes: 1

Related Questions