none
Stored Procedure behaves differently when activation is on?!

    Question

  • Hi,

    I have got a simple stored procedure for testing purposes. When Activation is off and I run this sp myself it works correct, but when I set activation to on and send a message, the message is being pulled from the queue and I think this can only be done by the activated sp but the insert into (see sp) is not done.

    here is stored procedure:

    as

    declare

    @message_bodyasxml;


    declare

    @message_typeassysname;


    declare

    @dialogasuniqueidentifier;


     


    while

    (1=1)


    begin


    waitfor (


    receivetop(1)


    @message_type=message_type_name,


    @message_body=cast(message_bodyasxml),


    @dialog=conversation_handle


    fromdbo.MADClaimCallHistoryQueue


    ),timeout2000;


     

    if (@@ROWCOUNT=0)


    begin


    print'rowcount = 0'


    break;


    end


    if (@message_type='http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')


    endconversation@dialog;


    elseif (@message_type='http://schemas.microsoft.com/SQL/ServiceBroker/Error')


    begin

    print'Dialog Error dialog #'+cast(@dialogasnvarchar(50));


    endconversation@dialog;


    end


    elseif (@message_type='InsertMessage')


    begin


    print'insert';


    insertintoMADLog.dbo.SBTest(xmlvar)


    select@message_body


    endconversation@dialog;


    end

    end

    Tuesday, January 28, 2014 11:27 AM

All replies