locked
catch sql exceptions in orchestration RRS feed

  • Question

  • Hi All,

    i am inserting recrds in sql using sp and if something goes wrong may be data issue, sp is not available, sql connection issue for this i am trying to catch Sql exception in orchestration. 

    tried below options, 

    set DeliveryNotification to None on send port with sql exception at catch.
    set DeliveryNotification to transmitted on send port with sql exception at catch.
    set DeliveryNotification to None on send port Tried system.exception at catch
    set DeliveryNotification to transmitted on send port with system.exception at catch.
    set DeliveryNotification to None on send port with generic fault at catch.

    on all above cases the exception is not getting catch by orchestration. and my logic in catch block is not getting executing.

    Can you please help me to solve this?

    Regards,

    Amit



    Regards, Amit More

    Tuesday, January 15, 2013 7:15 AM

Answers

  • Put your send and receive shape inside Scope with transaction type None.

    add New Exception Handler.

    Set Exception Type to System.Exception.

    also please check what exception you are getting in admin console

    Regards

    Mohit

    Tuesday, January 15, 2013 8:17 AM

All replies

  • Put your send and receive shape inside Scope with transaction type None.

    add New Exception Handler.

    Set Exception Type to System.Exception.

    also please check what exception you are getting in admin console

    Regards

    Mohit

    Tuesday, January 15, 2013 8:17 AM
  • Hi,

    You can use TRY/CATCH when you want to handle excpetions in the stored procedure, for example:

    BEGIN TRY
        -- ...
        -- Do Stuff
        -- ...
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    In the orchestration, catch SqlException

    Greetings,

    Tomasso Groenendijk

    Tuesday, January 15, 2013 8:20 AM
  • Hi 

    Try catching SOAPException once, Let me know if it works.


    HTH,
    Naushad Alam

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
    alamnaushad.wordpress.com

    Tuesday, January 15, 2013 9:45 AM
    Moderator
  • hi Naushad,

    we tried to catch generic soap exception but it didn't worked for me.

    regards,

    Amit


    Regards, Amit More

    Tuesday, January 15, 2013 9:50 AM
  • did u check in the event viewer, what is the exception type you are getting in the event viewer as an error .Try to add that exception handler to catch the exception

    Manish

    Tuesday, January 15, 2013 9:53 AM
  • exception type which i am getting is 

    System.Data.SqlClient.SqlException (0x80131904): Divide by zero error encountered.

    Server stack trace: 
       at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]: 
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)


    Regards, Amit More

    Tuesday, January 15, 2013 10:08 AM
  • Try to Add the divide by zero exception type in the exception handling as exception type object  and then put ur logic inside that .

    Manish

    Tuesday, January 15, 2013 10:40 AM
  • Also, You can add the SqlException as exception object to handle any sql error. You will get this from System.Data.

    Manish

    Tuesday, January 15, 2013 10:43 AM
  • i tried with the sql exception but it didn't worked. Divide by zero error encountered. is the message which i set in the sp its not a generic message.

    Regards, Amit More

    Tuesday, January 15, 2013 10:45 AM
  • Are you using the SQL Adapter , if yes then you need to catch Soap exception because sql adapter send exception in the Soap envelope. Try thisn  let me know


    Manish

    Tuesday, January 15, 2013 10:52 AM
  • Also, you can look on this link to catch the sql exception.

    http://sujant.wordpress.com/2008/06/24/biztalk-handling-sql-adapter-exceptions-to-resume-suspended-orchestrations/


    Manish

    Tuesday, January 15, 2013 10:54 AM
  • Mohit's below option worked for me, i did what mohit said and i rethrow the exception from catch block so it will accept by the outer main exception habdler.

    but what i don't understand, if it is failing then why exception is not caught by main/outer exception handler.

    Put your send and receive shape inside Scope with transaction type None.

    add New Exception Handler.

    Set Exception Type to System.Exception.

    also please check what exception you are getting in admin console

    Regards

    Mohit



    Regards, Amit More

    Tuesday, January 15, 2013 12:16 PM
  • Hi Amit

    You can set first Exception as SQLException and then outerexception as System.Exception

    if Exception catch under SQLException block then it will not goes to System.Exception Block

    Regards

    Mohit Gupta

    Tuesday, January 15, 2013 1:47 PM