• Home
• Technical
• Support
• Downloads
• More...

ErrorLog

Stores application Error logging entries.

@@@---@@@

This SQL script, t-ErrorLog, is responsible for creating and maintaining the persistent [dbo].[ErrorLog] table. This table is specifically designed to store detailed error logging entries for the dbOmnibus application, providing a dedicated place to record and manage application-level errors.

Script Breakdown
1. Update Persistent Entity Version Record
Metadata Declaration:

SQL

DECLARE @Name [varchar](128),@Description [varchar](128),@Version [int],@Type [varchar](5);
SET @Version=240829;
SET @Type='T';
SET @Name= 'ErrorLog';
SET @Description= 'Store application Error logging entries';
These lines declare local variables and assign metadata related to the [dbo].[ErrorLog] table. It sets the version of the ErrorLog table to 240829 (August 29, 2024).

EXEC [Deploy].[sp_UpdateEntityVer] @Type=@Type,@Name=@Name,@Description=@Description,@Version=@Version;: This statement calls the [Deploy].[sp_UpdateEntityVer] stored procedure. As analyzed previously, this procedure updates a temporary table (#EntityVers) with the version information for the ErrorLog table during the current deployment session. This temporary record will later be persisted to [dbo].[_EntityVersions] by [Deploy].[sp_SetEntityVersions].

GO: This separates the variable declarations and procedure call from the table creation block, ensuring they execute in distinct batches.

2. Table Creation ([dbo].[ErrorLog])
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ErrorLog]') AND type in (N'U')): This is a standard idempotent check. It ensures that the [dbo].[ErrorLog] table is only created if a user table with that name does not already exist in the dbo schema. This prevents errors if the script is run multiple times.

BEGIN CREATE TABLE [dbo].[ErrorLog]( ... ) ON [PRIMARY]; END;: If the table does not exist, it is created with the following columns:

[DateTime] [datetime] DEFAULT(getdate()) NOT NULL: This column stores the timestamp when the error occurred. It defaults to the current date and time (GETDATE()) and is NOT NULL, ensuring every error entry has a timestamp.

[Entity] [nvarchar](128) NULL: This column is intended to store the name of the entity (e.g., stored procedure, function, module) where the error originated.

[Message] [nvarchar](MAX) NULL: This column stores the full error message. nvarchar(MAX) is an excellent choice here, as error messages can vary greatly in length and content, including Unicode characters.

[ErrorNumber] [int] NULL: This column will store the SQL Server error number (e.g., from ERROR_NUMBER()).

[Reported] [bit] DEFAULT(0) NULL: This is a flag, likely used to track whether this error has been reported or processed (e.g., sent to an alert system, reviewed by an administrator). It defaults to 0 (false), indicating it's not yet reported.

Purpose and Role in the Framework
The [dbo].[ErrorLog] table serves as the application's dedicated error logging destination. Its primary purposes are:

Centralized Error Reporting: Provides a single, structured location for all application-level errors caught within the database.

Troubleshooting and Debugging: Critical for identifying, diagnosing, and resolving issues in a production environment. Developers and support staff can query this table to understand error patterns, frequency, and details.

Proactive Monitoring: The [Reported] flag allows for building monitoring solutions that can alert on new, unhandled errors.

Auditing and Analysis: Provides a historical record of errors, which can be useful for auditing compliance or analyzing application stability over time.

The sp_AddIndex procedure (from a previous script) might be used to add indexes to this table for efficient querying, especially on DateTime or Entity columns, though no explicit index creation is present in this specific script.

The usage example provided in the comments:

SQL

INSERT INTO [dbo].[ErrorLog] ([Entity], [Message], [ErrorNumber])
VALUES (@ErrorEntity, @ErrorMessage, @ErrorNumber);
This indicates how other parts of the application (typically CATCH blocks within stored procedures or functions) would populate this table.

This script establishes a fundamental and crucial component for any robust SQL Server application: a dedicated error logging system.