Sql-server – SQL Creating Error Log Table Benefits

error handlingsql serversql-server-2017stored-procedures

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

Best Answer

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

Related Solutions

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Sql-server – Column Name in separate table SQL Server

What you're looking for is Dynamic SQL Pivots. A PIVOT will turn row values into columns based on an aggregate, however you need to define the column names as part of the PIVOT, luckily we can do that on the fly with dynamic SQL. The following should generate the result set you want for a particular company (replace rowValues with your table name):

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()] 
                        FROM dbo.Headers H
                        WHERE H.company = @Company  
                        ORDER BY H.fieldNumber
                        FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT company,jobNumber,'+@Columns+'
FROM
(
    SELECT h.company,RV.jobNumber,RV.information,h.description
    FROM Headers h
            INNER JOIN dbo.RowValues RV
                ON RV.fieldNumber = h.fieldNumber
                   AND RV.company = h.company
    WHERE h.company = '+@Company+'  
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY jobNumber ASC'

EXEC sp_executesql @sql;

Unfortunately due to the possibility that different companies will have different columns there is no reliable way to combine all the the possible sets the above query could generate. Depending on how you want to use this query the easy option is to loop through all the companies and call an SP that contains the above, allowing you to output each company as a separate SELECT. Or you can do something with your SSIS package to output each one into a file.

If you want to insert the data into a table matching the calculated schema you can do a SELECT INSERT and a bit more dynamic SQL to get the data where it needs to go.

Best Answer

Related Solutions

Sql-server – deteriorating stored procedure running times

Sql-server – Column Name in separate table SQL Server

Related Question