none
messaging only - capturing stored proc errors in wcf sql adapter RRS feed

  • Question

  • Hi there,

    Can I please know how to capture stored proc errors when using a messaging only solution using wcf sql adapter ? No orchestration you see! Basically the idea is to capture error in stored proc and return it in the result which will then be used by the subscriber port to write to a file.

    If this ain't possible due the transaction msdtsc and biztalk engine conundrum :-) can somebody suggest another way of to achieve this in a messaging only biztalk solution?

    Thanks in advance

    Cheers

    Phanindra

    Monday, March 31, 2014 2:36 PM

Answers

  • Hi,

    I faced a similar issue just two or three days back. I had exception being thrown while executing a stored proc. What I did to solve this problem was that I enabled routing for failed messages. I had ESB toolkit installed on my environment and I was able to view the data for the exception thrown in (the fault table in esbdb (whatever can be the name of the db) in the sql server) I cant clearly recall the name of the table so pardon me :-) . What I did was

    1)Enable the fault message routing in the advanced transport options on the send port 

    2) Then go to the filters section on the send port and apply a filter 

    ErrorReport.ErrorType="FailedMessage" in logical OR with the filter to my receive location(This way it will help to subscribe to both the normal and failed messages). Then you can read the data from the database, there you can find the exception as well as other related details. you can then decide your further course of action.

    Or else if you can try using exception handling in stored proc and return the error.

    Please mark as answer if it helps you.

    Thursday, April 3, 2014 5:25 PM

All replies

  • On a Two-Way Send Port, the Adapter will publish a Fault if you have Propagate fault message checked on the Messages tab.

    You can subscribe to this.

    Is you solution already messaging only?  Otherwise, an Orchestration would be easier.

    Monday, March 31, 2014 3:03 PM
  • Hi mate,

    Thanks for the reply. Not sure how to use " Propagate fault message " property! As I tried switching it on and then off but it din't worked. I believe it isn't working because when stored proc throws an error, the send port is dehydrated because the retry count was 3.

    Setting it to zero, fails the send port and the failed message is now routed by biztalk ! But thanks you reply atleast got me thinking.

    As for the orchestration choice, with the latency and throughput issues. the idea is use orchestration only if no other option is available :-)

    Thanks again

    -P

    Monday, March 31, 2014 3:36 PM
  • Just to add, it still hasn't allowed me to send the error as part of the response xml ! Are you then suggesting that orchestration is the only way for that ?
    Monday, March 31, 2014 3:40 PM
  • Just came back to add something. It just soaked in that you're using the sqlBinding so there is no Fault message to Propagate, sorry :)  The Adapter is basically re-throwing the exception.

    So, you'll have to catch that in an Orchestration where you can get the Exception details.

    Monday, March 31, 2014 4:26 PM
  • Hi,

    If the error is in your stored procedure, then you can use the try-catch block in the stored procedure, catch the error then return the xml in response as you want and receive that response in BizTalk (message only scenario).

    Regards

    Tuesday, April 1, 2014 5:48 AM
  • Hi,

    I faced a similar issue just two or three days back. I had exception being thrown while executing a stored proc. What I did to solve this problem was that I enabled routing for failed messages. I had ESB toolkit installed on my environment and I was able to view the data for the exception thrown in (the fault table in esbdb (whatever can be the name of the db) in the sql server) I cant clearly recall the name of the table so pardon me :-) . What I did was

    1)Enable the fault message routing in the advanced transport options on the send port 

    2) Then go to the filters section on the send port and apply a filter 

    ErrorReport.ErrorType="FailedMessage" in logical OR with the filter to my receive location(This way it will help to subscribe to both the normal and failed messages). Then you can read the data from the database, there you can find the exception as well as other related details. you can then decide your further course of action.

    Or else if you can try using exception handling in stored proc and return the error.

    Please mark as answer if it helps you.

    Thursday, April 3, 2014 5:25 PM