We are conducting a datawarehouse project, moving data from Staging database to Kimball Data Warehouse.
What is the purpose of creating a custom stored procedure Error Log Table?
I am seeing a lot of websites recommend this. SQL server already has an Error log table in SSMS (Option a).
So why create another error log table, seems like additional coding? (Option b). I can even customize error messages using both options, SELECT @ErrMsg = 'testabcd'. Can someone explain? Thank you,
Note: Is there any automatic functionality in SSMS which logs errors for dbas? Is the only way to capture these errors is custom table? We are running code through sql agent jobs, and SSIS packages, thought we saw errors in agent job history and SSIS project catalog? issues like not null insert, value type conversion issues.
(a) SSMS Error Log Table
(b) Table and Stored Procedure https://www.tech-recipes.com/rx/53462/sql-server-error-logging-and-reporting-within-a-stored-procedure/
CREATE TABLE [dbo].[ErrorLogTable] ( [ErrorID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY, [ErrorNumber] [nvarchar](50) NOT NULL, [ErrorDescription] [nvarchar](4000) NULL, [ErrorProcedure] [nvarchar](100) NULL, [ErrorState] [int] NULL, [ErrorSeverity] [int] NULL, [ErrorLine] [int] NULL, [ErrorTime] [datetime] NULL ); CREATE PROCEDURE [dbo].[ErrorLogInsert] AS INSERT INTO [ErrorLogTable] ( ErrorNumber ,ErrorDescription ,ErrorProcedure ,ErrorState ,ErrorSeverity ,ErrorLine ,ErrorTime ) VALUES ( ERROR_NUMBER() ,ERROR_MESSAGE() ,ERROR_PROCEDURE() ,ERROR_STATE() ,ERROR_SEVERITY() ,ERROR_LINE() ,GETDATE() );