Service Broker Activation Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Other SQL Server 2012 Topics

Service Broker Activation Stored Procedure

Author

Topic

MCPieters
Starting Member

3 Posts

Posted - 2014-01-31 : 04:30:43

Hi,

I am in a test fase of service broker. I am making use of asynchronous triggers, so my messages are being send from a trigger.
where it goes wrong is when I activate my stored procedure that reads the messages from the queue.
alter queue ....
with activation (
status = on,
execute as self
)
with this code I set activation status on.

This is stored procedure that is being activated:
as
declare @message_body as xml;
declare @message_type as sysname;
declare @dialog as uniqueidentifier;

while (1=1)
begin
waitfor (
receive top(1)
@message_type = message_type_name,
@message_body = cast(message_body as xml),
@dialog = conversation_handle
from dbo.MADClaimCallHistoryQueue
), timeout 2000;

if (@@ROWCOUNT = 0)
begin
print 'rowcount = 0'
break;
end
if (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
end conversation @dialog;
else if (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
begin
print 'Dialog Error dialog #' + cast(@dialog as nvarchar(50));
end conversation @dialog;
end
else if (@message_type = 'InsertMessage')
begin
print 'convert xml';
insert into MADLog.dbo.SBTest(xmlvar)
select @message_body

end conversation @dialog;
end
end

this is just to test, of course this procedure is very simple...

but when activation is off and I execute procedure myself the insert into is done, but when activation is on the insert is not done.

But in both cases message is read from the queue.

My stored procedure is behaving differently, but why? Or is it nog activated? but why than is the message not still on the queue?

Can anyone help me?

Thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources