none
How to handle WCF-SQL exception in BizTalk 2010. RRS feed

  • Question

  • Hi Experts,

    I am having a scenario where I insert the order details to SqlServer through WCF_SQL adapter.

    I also have PRIMARY key set on order ID column.  When I drop duplicate message the SQL throws warning in event log.

    "The adapter failed to transmit message going to send port "Send2SQL" with URL "mssql://.//mydatabasename?". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_HDR4201_1'. Cannot insert duplicate key in object 'dbo.HDR4201'.
    The statement has been terminated."

    I want to handle this duplicate order scenario and I have orchestration built for this but instead of logging my custom error it still shows warning like above.

    Thanks in advance. /Dhiraj

    Tuesday, September 17, 2013 1:39 PM

All replies

  • To have the error immediately returned to the Orchestration, set the Retry Count on the Send Port to 0.

    That would happen eventually after the retries are exhausted.

    Tuesday, September 17, 2013 3:03 PM
  • Hi boatseller,

      I was expecting your reply and as expected you did.

    Thank you for that, but I did try it; still no luck. (Propagate fault message on the port is also checked.)

    Thanks/Dhiraj

    Tuesday, September 17, 2013 5:19 PM
  • Why do you need an Orchestration for this?

    The way I see it, you could apply your Map on the Send Port, make it a one-way Send Port, and just have BizTalk fail if you receive an error from the SQL Server. 

    Alternative you could enable Error Routing on the Send Port.

    Morten la Cour

    Tuesday, September 17, 2013 5:36 PM
  • So what exactly is happening?  The Orchestration should be Suspended/Resumable with the error details.

    As la Cour suggests, you only need the Orchestration of you intend to do something about the error.  If wll you want to do is log it, well, you have that in the Event Log.

    Tuesday, September 17, 2013 7:06 PM
  • Hi Guys,

    I could handle the duplicate scenario through the BRE but I just wanted to hands on with this fault behavior.

    Thanks for your timely help.

    /Dhiraj

    Wednesday, September 18, 2013 8:07 AM
  • Hi Dhiraj, 

    I am stuck with same scenario here. May I know What is the Exception type you have selected in Catch Block?

    If you don't mind can you share piece of code that you have used in Catch Block?

    what i need to basically do is that i need to insert record into exception table in sql when primary key violation error occurs. 

    Thank you. 

    @Venu

    venu.manchikanti@gmail.com

    Thursday, July 9, 2015 7:50 AM
  • Hi Dhiraj,

    What are exceptions you have handled over there?

    Thursday, July 9, 2015 10:51 AM
  • Hi Dhiraj,

    Previously i handled SOAP exception with WCF adapter ,i am not sure it ll work with WCF-SQL but you can try it.

    And also refer below link, It may help you.

    https://social.msdn.microsoft.com/Forums/en-US/e4959b2f-166c-40ea-8003-fcc6f6406dfb/wcfsql-exception-handling-in-orchestration?forum=biztalkgeneral


    Kind Regards, Anurag Prajesh (BizTalk Developer)


    Friday, July 31, 2015 5:42 AM