catch sql exceptions in orchestration

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

hi Naushad,

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

regards,

Amit

---

Regards, Amit More

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

Also, You can add the SqlException as exception object to handle any sql error. You will get this from System.Data.

---

Manish

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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