none
Catch SQL Server Send port one way WCF-SQL Exception in orchestration RRS feed

  • Question

  • Hi there,

    I have an orchestration, in a scope I want to send an insert to a SQL Table using the WCF-SQL Adapter. If the insert fails I want to send a Fault Message to the caller. However, the behavior of Biztalk Server 2010 is quite strange. Because, the insert fails, however BizTalk Server continues to execute, and sends a success to the caller, then just after, BizTalk Server enters the Catch exception.

    I have set the Delivery Notification to Transmitted, but it seems useless in this case.

    Did I miss something somewhere?

    Please see the orchestration below :

     

    Regards


    BizTalk Consultant in France
    Wednesday, October 5, 2011 4:30 PM

Answers

  • Hi Zia,

    There are two parts of the initial requirement, ie is that if the send of the response fails then also the fault message should be sent your skeleton orch wont work to fulfill that. I think he will be better placed to use send receive port and keep his orchestration as is.


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.


    Hi DPS Bali,

    I believe that you are talking about failure in sending the success response. Correct?

    If correct, then what sort of failure are you (I mean him) trying to catch here? The transaction will complete successfully once the message is successfully written to the message box and there is a subscriber available for that message. Transactional scope does not care what subscriber does with the message.

    If we are expecting the send failure of the success message, then wouldn't the same apply to the fault message as well (you have already mentioned that in one of your earlier response).

    I agree with you that request response is one way to receive ACK/NACK. The reason why I have not mentioned the use of Request - response is clearly because he is looking for an alternate solution. He seems to already have knowledge of request response approach, but for some reason doesn't want to use it.

    Bali,

    I don't want to use the request response, because I have nothing to do with the Response I don't care about it, so why should I use it? (only to check that every thing is ok? this should be done on the scope and the catch of the error)

    Regards,

    Stefan


    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Friday, October 7, 2011 11:21 AM

All replies

  • With Delivery Notification set to Transmitted you must has a exception handler setup to catch DeliveryFailureException, this exception does not bubble up to System.Exception like other .net exception do.

    I don't typically use Delivery Notification with SQL, I typically make it a 2-way port and if you get anything back it worked.


    Bill Chesnut | BizTalk Server MVP | Mexia Consulting | Melbourne Australia
    http://www.biztalkbill.com
    Please indicate "Mark as Answer" if this post has answered the question.
    Wednesday, October 5, 2011 10:00 PM
  • Thx Bill,

    It enters indeed the Delivery Notification exception (as it entered the General Eception), but after it already sent the response to the caller, calling the Exception is too late, because BizTalk Server continued with the orchestration and realized too late that there was indeed an exception with the message sent to SQL Server.

    I prefer to be an artist and find the best solution (which is when there is nothing else to substract from your code), using the request response is quite silly, because if there is an exception you cannot see the exception in the response.

    Hmmm, this is really not working as a logical programming? is it?

    Regards,

    Stefan


    BizTalk Consultant in France
    Thursday, October 6, 2011 7:48 AM
  • Hi,

    please set the Scope_2 to Synchronized=True in the properties window. This should do the trick.

    Best regards,

    Leo


    Please mark it as Answer if this answers your question.
    Thursday, October 6, 2011 8:20 AM
  • Thx Leo,

    I already tried this option, however I got this error : 'Transaction_2': a synchronized or atomic scope may not nest any transactional or synchronized scopes (in this case the error is due to the Scope_1, which I need to to use, in order to know if I already sent the Response message to caller, if no I can send the fault message). And dropping this Scope_1, how would I know if I can send the Fault message?. Am I missing something?

    Regards,

     


    BizTalk Consultant in France
    Thursday, October 6, 2011 8:46 AM
  • That explains your problem, is the send in an Atomic scope, if so, then BizTalk hold off sending the message to SQL until the scope completes, that is why you get the exception after you send the response to the client.

    Why are you using an Atomic scope?


    Bill Chesnut | BizTalk Server MVP | Mexia Consulting | Melbourne Australia
    http://www.biztalkbill.com
    Please indicate "Mark as Answer" if this post has answered the question.
    Thursday, October 6, 2011 9:04 AM
  • In fact I am using a Long Running Scope, does it change something?
    BizTalk Consultant in France
    Thursday, October 6, 2011 9:16 AM
  • Ok, I had another look at your screen shot in the 1st message, Scope_2 is LongRunning?

    What is the purpose and type of Scope_1?


    Bill Chesnut | BizTalk Server MVP | Mexia Consulting | Melbourne Australia
    http://www.biztalkbill.com
    Please indicate "Mark as Answer" if this post has answered the question.
    Thursday, October 6, 2011 9:34 AM
  • Hi,

    Why dont you use a request response port if you are using WCF SQL Adapter and Insert operation on the table. This would be best suited to your requirement.


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Thursday, October 6, 2011 10:04 AM
  • Bill,

    The purpose of scope_1 is to check if the response was successfully sent to the caller, and use the status of the scope_1's transaction to be able to send the fault message, otherwise I would get errors like : Cannot send before receive balblabla

    Regards,

    Stefan


    BizTalk Consultant in France
    Thursday, October 6, 2011 11:24 AM
  • Bali,

    I don't want to use the request response, because I have nothing to do with the Response I don't care about it, so why should I use it? (only to check that every thing is ok? this should be done on the scope and the catch of the error)

    Regards,

    Stefan


    BizTalk Consultant in France
    Thursday, October 6, 2011 11:25 AM
  • Stefan,

    Just trying to understand this, if you are not able to send the response message to the caller, you want to catch the exception and send the fault message to the caller. I think you will not be able to do so because if for any reason your response sending fails don't you think that your fault sending will also fail for the same reason.

     


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Thursday, October 6, 2011 11:40 AM
  • ... However, the behavior of Biztalk Server 2010 is quite strange. Because, the insert fails, however BizTalk Server continues to execute, and sends a success to the caller, then just after, BizTalk Server enters the Catch exception.

    In addition to my last post. If you use a request response port the orchestration will wait for the response from the insert. If you successfully receive the response the success will be sent to the caller. In case of any exception you will not get a response and the orchestration will directly go into the catch block and not execute the success send. Hope this makes sense and i understand the requirement correctly. Do tell me if I didnt anything correctly.
    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Thursday, October 6, 2011 11:44 AM
  • Bali,

    Did you already succeed in sending a fault message in the resquest send port? If you didn't the way to do it is in the image above, because your suggestions do not really fit.

    So basically, you use a scope 2 , inside the scope 2 you set another scope 1 used to make sure the Response was already sent to the caller or not.

    In the catch, you check weither scope 1 succeeded? if so, you do not send the fault, if not, you send the fault, and this is the if in the catch block, so you are always sure the caller gets a response!

    Regards,

     

     


    BizTalk Consultant in France

    Thursday, October 6, 2011 12:54 PM
  • To answer your question yes. and I get the point about making sure that the caller always gets a response. But to get the desired behavior from delivery notification = transmitted (on SQL Send) it has to be in a synchronized scope, only then the orchestration will wait for ACK or NACK for the transmitted message.
    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    • Proposed as answer by Racha Rams Thursday, October 6, 2011 1:37 PM
    • Unproposed as answer by BizTalk - Paris Thursday, October 6, 2011 2:12 PM
    Thursday, October 6, 2011 1:28 PM
  • The purpose of scope_1 is to check if the response was successfully sent to the caller, and use the status of the scope_1's transaction to be able to send the fault message, otherwise I would get errors like : Cannot send before receive balblabla

    Get rid of Scope_1 and use the logic as in image below. Hope this helps.

     


    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Thursday, October 6, 2011 2:47 PM
  • I am not sure whether this will fit your requirement. Try wrapping the SQL send port inside an atomic scope.

    Thanks


    SKGuru
    Thursday, October 6, 2011 2:53 PM
  • Hi SKGuru,

    I have quickly created a skeleton orch to help him with the following error: "must receive before sending a fault message on an implemented port". :)

    He used Scope_1 (Atomic) just for this reason.

    Regards,

    Zia


    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Thursday, October 6, 2011 3:00 PM
  • Hi Zia,

    There are two parts of the initial requirement, ie is that if the send of the response fails then also the fault message should be sent your skeleton orch wont work to fulfill that. I think he will be better placed to use send receive port and keep his orchestration as is.


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Thursday, October 6, 2011 8:13 PM
  • Hi Zia,

    There are two parts of the initial requirement, ie is that if the send of the response fails then also the fault message should be sent your skeleton orch wont work to fulfill that. I think he will be better placed to use send receive port and keep his orchestration as is.


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.


    Hi DPS Bali,

    I believe that you are talking about failure in sending the success response. Correct?

    If correct, then what sort of failure are you (I mean him) trying to catch here? The transaction will complete successfully once the message is successfully written to the message box and there is a subscriber available for that message. Transactional scope does not care what subscriber does with the message.

    If we are expecting the send failure of the success message, then wouldn't the same apply to the fault message as well (you have already mentioned that in one of your earlier response).

    I agree with you that request response is one way to receive ACK/NACK. The reason why I have not mentioned the use of Request - response is clearly because he is looking for an alternate solution. He seems to already have knowledge of request response approach, but for some reason doesn't want to use it.

    Bali,

    I don't want to use the request response, because I have nothing to do with the Response I don't care about it, so why should I use it? (only to check that every thing is ok? this should be done on the scope and the catch of the error)

    Regards,

    Stefan


    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Friday, October 7, 2011 11:21 AM
  • Hi Zia,

    Just saw this , Well the same thing I tried to explain in the earlier post and agree with you. It was the same thing I was trying to reason out with the thread initiator. 


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Monday, October 17, 2011 11:58 AM