Re-raise error without the throw statement

Question

Re-raise error without the throw statement

Stephanie Giovannini

SSCertifiable

Points: 7422
More actions
September 21, 2016 at 3:34 pm

#329959

I'm trying to troubleshoot an error message that was returned by a stored procedure. The error message is this:
Error 2754 caught at line 145: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
I suspect that this error came out of the catch block and is hiding the real error. The catch block of this stored procedure (and every other stored procedure in this database) looks like this (@error_number is declared higher in the procedure):
BEGIN CATCH
DECLARE
@error_message nvarchar(2048)
, @error_severity int
, @error_state int
, @error_line int;
SELECT
@error_number = ERROR_NUMBER()
, @error_message = ERROR_MESSAGE()
, @error_severity = ERROR_SEVERITY()
, @error_state = ERROR_STATE()
, @error_line = ERROR_LINE();
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END;
RAISERROR('Error %d caught at line %d: %s'
, @error_severity
, @error_state
, @error_number
, @error_line
, @error_message);
END CATCH;
Many of the stored procedure calls are nested, so an error might go through several catch blocks on its way to the caller, which logs the error. What I really want here is THROW, but this is 2008 R2 and it's not available. :angry:
I believe that the original error has a severity above 18. I don't have any clue as to what that error might be. This particular stored procedure (and all the procedures that it calls, there are quite a few) is called every 15 seconds. Over three days of running, the error only occurred once. I'm actually more concerned about the error being hidden than the error that was hidden (which was likely a transient issue).
How should the catch block be written in SQL 2008 R2? What's the best way to implement THROW when one can't have THROW?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 1

I did find the "hidden" transient error. It's in the SQL Server error log and occurs three times at the exact same second as the other error was logged.

[298] SQLServer Error: 1204, The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. [SQLSTATE HY000] (ConnIsLoginSysAdmin)

I don't know what caused this, but since this is a development-only system and the error was transient, I'm going to let it go. But I still want to fix the catch block so that it doesn't do this.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

I guess one solution would be to raise the error again with a user error level, but include the original error level in the message?

By the way, unrelated, but you should be careful with ROLLBACK in a catch block - check out the documentation for XACT_STATE or you could get more problems.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);