In my application the entire logic is in stored procedure covering 10 to 15 tables. The problem iam facing is when ever an small error occurs in stored procedure(say line 5 of 150) its going to catch block, but iam unable to get that information back from sql adapter.
when ever an error occurs the biztalk suspends the messge and when i look in to event viewer, its not showing the correct error information.
example: when inserting a value into an identity column it goes to catch block and generates the messge "cannot insert value into identidy column", i need to back get this messge exactly from sql adapter as output value
Plz help me.
Are you using Try/Catch blocks in your SP code (this works on SQL 2005 & 2008 but not SQL 2000)? If not, I suggest you rewrite your SP using Try/Catch blocks and utilize the RAISERROR function to throw custom alerts from the SP. That should override the default alerts it looks like you're seeing from the SQL adaptor.