1

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

enter image description here

(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()  
       );
2

The picture (a) shows us not "SSMS error log table", it's the content of SQL Server error log file.

The errors occured at the service and server level are stored here.

The examples are: service could not start because the port was alredy occupied, system database files were not found, max server memory was configured to 0, etc. The errors ot this type cannot be catch by your procedure just because no procedure can be executed as service could not start.

Other kinds of errors stored in error log such as login failed or session was killed also cannot be catch by your code.

Your error logging proc is intended to catch other types of error such as constraint violation, conversion errors, permission errors, all these types of error are NOT logged in the SQL Server error log as they are simply programming errors.

You did not say that your code is executed in jobs and that there are ssis packages. In SSIS you can enable logging (it's not enableds by default), besides your code can have try..catch blocks where you want to suppress the errors and continue code execution.

There is no other functionality in SSMS which logs errors for dbas. SQL Server error log supplies errors for DBA regarding server functionality. The procedure that you showed is for developers, the errors it can catch do not influence server life, they regard only your code execution, and there is no "default" logging mechanism that catches and logs programming errors

  • 1
    Two comments: 1) You can configure whether a message in sys.message (an "error") ends up in the eventlog/errorlog, using sp_altermessage. SQL Server does in most cases respect this setting (as seen in sys.messages.is_event_logged. 2) You can have a trace running capturing errors. either Extended Events trace or using the old-school SQL Trace functionality. – Tibor Karaszi Feb 22 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy