locked
Capturing SQL Exception in Orchestation RRS feed

  • Question

  • Iam reading from sql server using sql adapter.immediately after reading i send a delete command using two way send port. The whole process is within a scope. This is working fine if the polling interval is more than 30 secs. If i bring down the polling time then it gives the error: no deletable rows found and a suspended orchestration is found. Can i catch this exception and terminate the orchestration. I tried system.exception but it is not working. Any help is appreciated. Thanks
    Wednesday, May 5, 2010 12:54 PM

Answers

All replies

  • Catching General Exception should work to catch any exception occurs inside your scope shape. can you give the actual error you are getting. I also suggest check if your SQL Server is giving low performance.


    Please mark it as Answer if this answers your question
    Thanks.
    Mo
    The contents I write here is my personal views, not the view of my employer and anyone else.
    Wednesday, May 5, 2010 1:34 PM
  • Try to do the following things:

    1.Check what is the transaction type for the scope, if it is none then 

    change it from  transaction type=none to Long Running.

    2. modify the Send port Delivery Notification from None to Transmitted.

    3. Now catch the System.Exception.

    It should work..

    Regards,

    Abhijit


    Abhijit Mahato Please "Mark as Answer" if Post has Answered the Question
    Wednesday, May 5, 2010 1:39 PM
  • Thanks for reply.

    Iam using role links to do the select and delete.I don't see the option to set the delivery notification.

    Also if I want to extract the exact error under system.exception what is the command?

     

     

     

    Wednesday, May 5, 2010 4:06 PM
  • You should be able to catch a SQL exception and look at the error code to terminate the orchestration. I suggest you just let the orchestration end rather than use a terminate shape because you will probably get a zombie message with a terminate shape.

    Here is a link on SQLException: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception_members.aspx. The ErrorCode would be useful for you. You can lookup error codes here: http://www.microsoft.com/technet/support/ee/ee_advanced.aspx.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, May 6, 2010 3:19 AM
    Moderator
  • The Oracle adapter has an option of " Delete After Poll".Iam trying to simulate this in SQL adapter. Iam getting the following error: Description: The adapter "SQL" raised an error message. Details "HRESULT="0x80040e14" Description="SQLOLEDB Error Description: Empty delete, no deletable rows found Transaction aborted " <Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server" Description="SQLOLEDB Error Description: Empty delete, no deletable rows found Transaction aborted "?></Root>". Iam trying to capture this error and end the orchestration. So I created a scope with synchronized true and transaction none. Added the exception and inside exception writing to event log. It seems the orchestration does not go into exception loop since iam not seeing the event log. What are the other settings needed? Thanks
    Thursday, May 6, 2010 4:12 AM
  • I think if you are using OLE DB you would need to use OleDbException rather than SqlException. http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbexception.aspx. Are you using OleDbException?

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, May 7, 2010 2:42 PM
    Moderator
  • I would suggest to make a select and delete in one stored procedure, maybe in one transaction depends of the business logic. Then call the stored procedure from the BizTalk.

    It is not a good idea to manage this situation with delays.


    Leonid Ganeline [BizTalk MVP] Biztalkien blog
    Sunday, May 9, 2010 4:04 AM
    Moderator
  • Stored procedure is not option in my scenario. So I have to try out capturing the error and termination the orchestration. I tried General exception,oledb exception and SQL exception but none of these capture this error. I have given a long running scope. Any help to capture this error: "The adapter "SQL" raised an error message. Details "HRESULT="0x80040e14" Description="SQLOLEDB Error Description: Empty delete, no deletable rows found Transaction aborted " <Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server" Description="SQLOLEDB Error Description: Empty delete, no deletable rows found Transaction aborted "?></Root>". Thanks
    Wednesday, May 12, 2010 6:30 AM
  • Iam using the SQL Adapter with two way sendport. request to delete and a response to indicate success.
    Wednesday, May 12, 2010 6:38 AM
  • Try capturing SOAP exception. I have always used SOAP exception to catch all the sql exceptions in the orchestration.

    Nikhil

    Dont forget to mark as answer if it answeres.

    Wednesday, May 12, 2010 8:37 AM
  • I tried soap exception also still not able to trap the error. I am using role links for connecting to SQL. is this the reason?
    Wednesday, May 12, 2010 11:30 AM
  • Not sure that Role links would create the problem. Did you try setting the delivery notification to transmitted? You will get this in the properties of Role link.

    • Edited by Nikhil J Wednesday, May 12, 2010 12:30 PM Typo
    Wednesday, May 12, 2010 12:28 PM
  • Relying on the exception to control flow is a bad practice. Use one way port or simply ExecuteScalar() from .Net component. Better option would be to place all polling/deleting logic into stored proc. If it's not available, you can write everything in polling statement.


    http://geekswithblogs.net/paulp/
    Wednesday, May 12, 2010 9:52 PM
    Answerer
  • how to write more than 1 polling statement in the receive location thanks
    Thursday, May 13, 2010 6:16 AM
  • Hi Harik,

    Concerning SQL Adapter and polling statements I suggest reviewing Best Practices for SQL Adapter.

    HTH

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly

     


    BizTalk
    Friday, May 21, 2010 10:32 PM
    Moderator