none
SEND Message ?

    Pergunta

  • I need help in reference to XML.

    My SEND ON Conversation is in Store PROC & i want pass message as parameter to this store proc.
    I want to know if there is any way i can pass to store proc in reference to XML

    Declare @SendMsg XML;

    Set @SendMsg = N'
    <ROW>
    <LogID>' + cast(@logid as Varchar(50)) + N'</LogID>
    <SessionID>' + @sessionid + N'</SessionID>
    <UserName>' + @username + N'</UserName>
    <UserType>' + @usertype + N'</UserType>
    <Service>' + @Service + N'</Service>
    <Method>' + @Method + N'</Method>
    <Parameter>' + @Parameter + N'</Parameter>
    <AuditType>' + @Audittype + N'</AuditType>
    <AuditValue>' + @AuditValue + N'</AuditValue>
    <QueryTime>' + CAST(@QueryTime AS varchar(50)) + N'</QueryTime>
    <ApplicationName>' + @ApplicationName + N'</ApplicationName>
    <IPAddress>' + @IPAddress + N'</IPAddress>
    </ROW>';

    exec [usp_SSB_send_from_Initiator] N'InitiatorService', N'TargetService', N'Contract', N'Message Type', N'@SendMsg'

    FYI:-

    --CREATE PROCEDURE [usp_SSB_send_from_Initiator] (

    --      @fromService varchar(555),

    --      @toService varchar(555),

    --      @onContract varchar(555),

    --      @messageType varchar(555),

    --      @messageBody XML)

    Any help is highly appreciated.


    Thanks

    sexta-feira, 9 de janeiro de 2009 21:49

Respostas

Todas as Respostas

  • exec [usp_SSB_send_from_Initiator] N'InitiatorService', N'TargetService', N'Contract', N'Message Type', @SendMsg;


    http://rusanu.com
    sexta-feira, 9 de janeiro de 2009 21:57
    Moderador
  • aaditya2000 said:

    Set @SendMsg = N'
    <ROW>
    <LogID>' + cast(@logid as Varchar(50)) + N'</LogID>
    <SessionID>' + @sessionid + N'</SessionID>
    <UserName>' + @username + N'</UserName>
    <UserType>' + @usertype + N'</UserType>
    <Service>' + @Service + N'</Service>
    <Method>' + @Method + N'</Method>
    <Parameter>' + @Parameter + N'</Parameter>
    <AuditType>' + @Audittype + N'</AuditType>
    <AuditValue>' + @AuditValue + N'</AuditValue>
    <QueryTime>' + CAST(@QueryTime AS varchar(50)) + N'</QueryTime>
    <ApplicationName>' + @ApplicationName + N'</ApplicationName>
    <IPAddress>' + @IPAddress + N'</IPAddress>
    </ROW>';


    BTW, just use FOR XML PATH:

    DECLARE @SendMSG XML;
    SELECT @SendMSG = (SELECT logid as [LogID], @sessionid as [SessionID], ..., @IPAddress as [IPAddress] FOR XML PATH('ROW'), TYPE);
    ...
    exec ..., @SendMSG;


    http://rusanu.com
    sexta-feira, 9 de janeiro de 2009 22:01
    Moderador
  • Hello Remus:

    Thanks for the reply, earlier i did that EXEC ..............................,@SendMsg & it didn't worked & but now it is working. very strange.

    After executing this store proc
    exec [usp_SSB_send_from_Initiator] N'InitiatorService', N'TargetService', N'Contract', N'Message Type',@SendMsg;

    I see my messages stuck at Initiator Transaction_Queue & Tranmission_Status is empty

    Any pointer is appreciated.

    Thanks


    sexta-feira, 9 de janeiro de 2009 22:08
  • http://rusanu.com/2005/12/20/troubleshooting-dialogs/
    http://rusanu.com/2007/11/28/troubleshooting-dialogs-the-sequel/

    http://rusanu.com
    sexta-feira, 9 de janeiro de 2009 22:16
    Moderador
  • Hello:
    (http://blogs.msdn.com/sql_service_broker/archive/2008/07/25/reusing-dialogs-with-a-dialog-pool.aspx)

    After getting better understanding about this article (above mentioned link). I created Dialog Table & required Store Procs. I didn’t created this table “CREATE TABLE MsgTable” on TARGET DATABASE. As I thought that was not needed.

     

    I SEND my message from INITIATOR to TARGET & the message get stuck in Initiator – Transmission_Queue with TRANSMISSION_STATUS as empty\blank. I did profiler on target & initiator & see no event on Target for following:-

    Broker/Broker:

    Conversation, Message Undeliverable and Remote Message Acknowledgement.

     

    On Initiator Database, I run the DMV to get locks status & I saw “CONVERSATION_GROUP” lock for that SPID on resource_type = METADATA.

    Its my understanding that LOCK should get released once message is send, but it is still holding. if i SEND 2nd message i see that in Initiator – Transmission_Queue.

    every thing is set as required. before reusing DIALOG, SEND messages were working fine.

    Any pointer is appreciated.

    Thanks
    segunda-feira, 12 de janeiro de 2009 19:24
  • I was abled to get resolved.

    Thanks
    • Marcado como Resposta aaditya2000 segunda-feira, 12 de janeiro de 2009 20:45
    segunda-feira, 12 de janeiro de 2009 20:45