Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bpears
Starting Member
2 Posts |
 Posted - 2014-09-18 : 11:43:31
|
| Hi there...We have a stored proc that first deletes certain rows from a table and then inserts new rows on a nightly basis via a job. Recently, someone removed a column from a table and forgot to change the insert statement in this Stored Proc. Some users were unable to access some systems they normally would be able to because the delete portion of the SP worked fine, but it failed on the insert.. Upon checking into the issue, the SP does have "SET XACT_ABORT ON" in the code but after doing a little research, it appears that a xaction rollback does not happen on syntactical errors when using xact_abort, and this is what we got when the column was removed... In fact, even when looking at the SQL job history... It showed the job completing successfully!!!! I have been doing a little testing with a try/catch block etc...and am unable to get it to rollback when there is an issue like this...What can one do to catch syntax errors, rollback the entire xaction and report back that the job failed?Thanks, Brad |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-18 : 14:13:34
|
| Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. TRY/CATCH doesn't handle syntax errors. How could the stored procedure even have been compiled if there are syntax errors? Is it in dynamic SQL?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
bpears
Starting Member
2 Posts |
Posted - 2014-09-19 : 09:12:08
|
Hi Tara, thanks for the reply. No, it's not dynamic SQL. It's just a a "standard" stored procedure. It has been in production as a nightly job and was working just fine. Then, during some system changes, a column in the database table that this procedure was inserting rows into was removed and the procedure was missed during the changes. So later that night, the procedure fires up, managed to run the first part which was a "delete from {tablename} where this = that" but then could not run the insert because the columns did not match up... The job did NOT report a failure, it reported success, except it never did successfully complete! A little bit of a flaw there somewhere... There has to be an easy way to catch these types of issues one would think... When I got the problem to look into yesterday, I discovered that there were missing rows so that's when I looked into the SP that inserts these rows and discovered it was incorrect... I don't understand how it could possibly have run and reported success in the job log!!! How can that be? Bizarre I'd say!Bradquote:
Originally posted by tkizer
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. TRY/CATCH doesn't handle syntax errors. How could the stored procedure even have been compiled if there are syntax errors? Is it in dynamic SQL?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
|
|
|
|
|
rolling back a xaction on syntax errors