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