Post reply

SQL Server error handling

  • June 1, 2014 at 3:08 am

    #286032

    Hello,

    I am trying to process 3 XML files into DB table using a TRY/CATCH in while loop.

    What I want is, if there is an error processing any one of XML, I want SP to continue processing other XML files and at the same time I want to raise an error for unprocessed XML file. I have achieved this in stored procedure but when I run this SP in SQL Server Agent job, it runs smoothly without any error but I want this job to fail because of unprocessed XML.

    Example:

    XML 1 - parsed and inserted in DB table

    XML 2 - Error

    XML 3 - parsed and inserted in DB table.

    I can see error in CATCH block for XML 2 while I run SP in SSMS. XML data(XML 1 and 3) successfully inserted into DB table.

    However, if I run this SP in job it runs without any error. I want this job to fail or return error and also want it to insert data in Db table from XML 1 and XML 3.

    How can I achieve this? Please help me with this.

    Your sample code will help greatly.

  • June 1, 2014 at 4:39 am

    #1718057

    Check out this article XML Schema validation in SQL Server[/url], the last part demonstrates similar error handling.

    😎

  • June 1, 2014 at 5:00 am

    #1718058

    Hi Eirikur Eiriksson,

    Thank you for your response.

    I have implemented something similar. I have created new table to log errors and if i find some new errors logged in the table at the end of SP then I raise error. So in this way i continue processing other files and log errors if any and at the end of SP, if any errors logged, then I raise an error so that the agent job also fails.

    Again thanks for your response.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply