Stored procedure aborts without message

Question

Stored procedure aborts without message

juute

SSC Enthusiast

Points: 169
More actions
March 2, 2011 at 2:49 am

#147411

Hello everyone,
a strange behaviour occurs in our daily load procedures.
We log all stored procedures with beginning time, end time, error messages, failure/success status etc. in a table.
Sometimes, a stored procedure aborts without message and failure/success status.
But there were no changes in this stored procedure and it's not always the same procedure.
Do you have any suggestions?
Thanks in advance, Andre

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

we'd be able to help better if you showed us the body of the stored proc; but in general, if you get an error that is level 16 or above, all processing stops, and so your code which logs the times would never execute;

examples I can think of would be:

1. dynamic sql with a bad syntax error.

2. primary key insert errors

3. unique constraint errors

4. foreign key constraint errors

5. logical errors like expecting a single value froma select... like a WHERE statment with SomeValue = (select PossiblyTwoRows from....

6, a badly written trigger on the source table rolls back due to an error, causing the stored proc and it's logging to roll back with it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

juute SSC Enthusiast Points: 169 More actions · Answer 2

Thanks for your quick response!

The structure of a single stored procedure is alwas the same:

Header (setting variables for the logging table)

Begin try-block

Standard SQL-statements (like Inserting new rows into a table)

End try-block

Begin catch-block

Catching error messages (setting variables for the logging table)

End catch-block

The header and catch blocks are exactly the same in every stored procedure.

There are no dynamic SQL-statements within the try-blocks, the code didn't change and there are no triggers.

Normally, the error message will be inserted into the logging table, even if a primary/foreign key error or unique constraint error occurs.

But sometimes one stored procedure aborts abnormally and it's not a certain one.

Kind regards, Andre