CODEWITHSUNDEEP
sql.netsql-servernservicebus
James Radford
James Radford

Reputation: 1855

Extracting messages from SQL Server Service Broker

How do you extract messages from SQL Server Service Broker please? I'm using nservicebus.

I have messages in my SQL Server Service Broker queue but I'm not sure how to process them.

many thanks,

Upvotes: 2

Views: 3281

Answers (4)

Naruto
Naruto

Reputation: 708

This code will help you. DECLARE @messageType SYSNAME DECLARE @conversationHandle UNIQUEIDENTIFIER DECLARE @Handle UNIQUEIDENTIFIER DECLARE @MessageBody Nvarchar(max)

DECLARE @conversation_group_id UNIQUEIDENTIFIER ;

WAITFOR(
GET CONVERSATION GROUP @conversation_group_id
     FROM [UpdateReceiveQueue]
     );

WAITFOR (
      RECEIVE TOP(1)
            @messageType=message_type_name,
            @MessageBody=message_body,
            @conversationHandle=conversation_handle
            FROM [UpdateReceiveQueue] where conversation_group_id = @conversation_group_id
),timeout 2000;    

print @MessageBody

Please use this link to get more information.

Upvotes: 1

Alexey Zimarev
Alexey Zimarev

Reputation: 19640

NServiceBus does not support SSSB as a transport. The NServiceBus SQL Server Transport uses tables as queues with polling.

I created my own SSSB processing based on IAdvancedSatellite. However, SSSB appears to be not very reliable and we stopped using it. The reasons were:

  1. SSSB is turned off on database restore, doesn't matter if is was off when the db was backed up
  2. SSSB fails silently, so when it gets eventually disabled, published messages are just swallowed and disapper in /dev/null without any errors
  3. If an advanced satellite goes down, the NServiceBus keeps running but does not process your SSSB messages. You must take care to recover the satellite yourself. When using a transport though, when it goes down the whole service gets consequences via critical failure processing
  4. On some SQL Servers, SSSB got some denied access errors that were published to the Windows event log. If no limit is set on application eventlog, it fills up the whole disks and server crashes.

So, I would rather recommend using the strandard SQL Server transport if you must use SQL Server. However you should remember that it is polling your database every second.

Upvotes: 0

Deepak.Aggrawal
Deepak.Aggrawal

Reputation: 1277

I am not sure but I think these links would help you :

http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/

Integrating SQL Service Broker and NServiceBus

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

The only way to extract messages from a Service Broker queue is the RECEIVE statement. Service Broker has Activation that can trigger the code that runs the RECEIVE statement.

Upvotes: 1

Related Questions