September 7, 2017 at 10:50 am
Hi,
We have a Stored Procedure being run by an SSIS Package which goes through records in a table (Table A) and inserts/updates records in another table (Table B). Once the Insert/Update has finished the records in Table A are stamped with today's date. We have had an instance where the Insert has failed correctly due to the conditions of an index being breached on Table B however Table A has still been stamped with today's date and the rest of the Stored Procedure has been completed. The Procedure is broken down into 4-5 Begin and End sections and I just wanted to understand if it is normal for the procedure to complete even though part of it has failed.
Thanks
Matt
September 7, 2017 at 11:47 am
Yup. Normal behaviour. Most errors terminate the statement, not the batch.
If that's not what you want, you need some error handling, and probably some transactions.
http://www.sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
The blog post talks about transactions, but the behaviour's the same with or without transactions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2017 at 4:09 am
Hi GilaMonster,
Thanks for the response, it actually works in our favour I think as the procedure sends an email at the end so we can see where the Insert stopped.
Cheers,
Matt
September 8, 2017 at 4:41 am
You can have error handling and transactions to roll back partial operations and still have it email you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy