How to handle WCF-SQL exception in BizTalk  2010.

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

September 17th, 2013 1:39pm

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.

Free Windows Admin Tool Kit Click here and download it now
September 17th, 2013 3:03pm

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

September 17th, 2013 5:19pm

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

Free Windows Admin Tool Kit Click here and download it now
September 17th, 2013 5:36pm

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

September 17th, 2013 5:36pm

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

Free Windows Admin Tool Kit Click here and download it now
September 17th, 2013 5:36pm

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

September 17th, 2013 5:36pm

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

Free Windows Admin Tool Kit Click here and download it now
September 17th, 2013 5:36pm

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

September 17th, 2013 5:36pm

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.

Free Windows Admin Tool Kit Click here and download it now
September 17th, 2013 7:06pm

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

September 18th, 2013 8:07am

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

Free Windows Admin Tool Kit Click here and download it now
July 9th, 2015 3:52am

Hi Dhiraj,

What are exceptions you have handled over there?

July 9th, 2015 6:53am

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

Free Windows Admin Tool Kit Click here and download it now
July 31st, 2015 1:46am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics