Geek Answers Handbook

How to get error_message with SQL Server TRY ..... CATCH block

BEGIN TRY BEGIN TRANSACTION --Lots of T-SQL Code here COMMIT END TRY BEGIN CATCH ROLLBACK USE [msdb]; EXEC sp_send_dbmail @profile_name='Mail Profile', @recipients=' myEmail@mydomain.org ', @subject='Data Error', @body = SELECT ERROR_MESSAGE(); END CATCH

I get the following error on this line

 @body = SELECT ERROR_MESSAGE();

Invalid syntax next to the keyword "SELECT".

Does anyone know why?

+6
source share
3 answers

You cannot issue a SELECT statement directly to a stored procedure parameter. Do something like this:

 DECLARE @err_msg AS NVARCHAR(MAX); SET @err_msg = ERROR_MESSAGE(); EXEC sp_send_dbmail @profile_name='your Mail Profile here', @recipients=' myEmail@mydomain.org ', @subject='Data Error', @ body=@err _msg
+11
source

Please try this

  SET @body = ERROR_MESSAGE()
+5
source

See this block of code. May help you a bit with exception handling at the end of Sql.

 BEGIN TRY -- RAISERROR with severity 11-19 will cause execution to -- jump to the CATCH block. RAISERROR ('Error raised in TRY block.', -- Message text. 16, -- Severity. 1 -- State. ); END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. -- RAISERROR (@ErrorMessage, -- Message text. -- @ErrorSeverity, -- Severity. -- @ErrorState -- State. -- ); EXEC sp_send_dbmail @profile_name='Mail Profile', @recipients=' myEmail@mydomain.org ', @subject='Error Refreshing PMT Database', @body = @ErrorMessage; END CATCH;
+2
source

More articles:


All Articles