Logging Errors to SQL logs via RAISERROR WITH LOG

Logging errors to SQL logs via RAISERROR >

1) The Code -

RAISERROR('Test of custom error logging', 18, 1) WITH LOG

2) Management Studio Results

3) Corresponding entry in the SQL Server log

Expanding on this we can pass the genuine error message through, like this -
1) The Code -

BEGIN TRY
   BEGIN TRANSACTION 

 -- Do action that we want rolled back if an error occurs
 DELETE Person.Address WHERE AddressID = 1  

   COMMIT
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK

 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @ErrorSeverity INT

 SELECT
  @ErrorMessage = ERROR_MESSAGE(),
  @ErrorSeverity = ERROR_SEVERITY()

 RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG
 
END CATCH

2) The SQL log -

You can do a variety of Error logging methods here in this way -
1 ) send email
2 ) write to table
3 ) log to sql via RAISERROR (as abover)

You have access to the following error functions -

ERROR_NUMBER() - returns the number of the error.
ERROR_SEVERITY() - returns the severity.
ERROR_STATE() - returns the error state number.
ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() - returns the line number inside the routine that caused the error.
ERROR_MESSAGE() - returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.


link : http://technet.microsoft.com/en-us/library/ms175976.asp