Simple message logging

This article describes an approach to one-line, simple event logging from within stored procedures; something like this:

EXEC dbo.usp_LogEvt 'Here is a message', @@PROCID
  1. Create a log table in a convenient administration database. Mine is called _Admin but the name isn't important – if you already have one you can use that instead.

    USE _Admin
    GO
     
    IF OBJECT_ID('dbo.EventLog') IS NOT NULL
      DROP TABLE dbo.EventLog
     
    GO
     
    CREATE TABLE dbo.EventLog (
      EventId INT IDENTITY(1,1) PRIMARY KEY
    , EventTimestamp DATETIME NOT NULL DEFAULT GETDATE()
    , SourceDb VARCHAR(128) NOT NULL
    , SourceSpSchema VARCHAR(128)
    , SourceSpName VARCHAR(128)
    , [Message] VARCHAR(MAX) NOT NULL
    )
  2. Create an SP in the same database to write entries into the log

    USE _Admin
    GO
     
    IF OBJECT_ID('dbo.usp_MakeLogEntry') IS NOT NULL
      DROP PROCEDURE dbo.usp_MakeLogEntry
     
    GO
     
    CREATE PROCEDURE dbo.usp_MakeLogEntry (
      @srcDb VARCHAR(128)
    , @srcSpSchema VARCHAR(128)
    , @srcSpName VARCHAR(128)
    , @msg VARCHAR(MAX)
    )
    AS
    BEGIN
     
      INSERT INTO dbo.EventLog (
        SourceDb
      , SourceSpSchema
      , SourceSpName
      , [Message]
      ) VALUES (
        @srcDb
      , @srcSpSchema
      , @srcSpName
      , @msg 
      )
     
      RETURN SCOPE_IDENTITY()
     
    END
  3. usp_MakeLogEntry is not intended to be called directly – create this wrapper SP:

    IF OBJECT_ID('dbo.usp_LogEvt') IS NOT NULL
      DROP PROCEDURE dbo.usp_LogEvt
     
    GO
     
    CREATE PROCEDURE dbo.usp_LogEvt (
      @msg VARCHAR(MAX)
    , @objId INT = NULL
    )
    AS
    BEGIN
     
      DECLARE @dbName VARCHAR(128) = DB_NAME()
      DECLARE @schemaName VARCHAR(128) = OBJECT_SCHEMA_NAME(@objId)
      DECLARE @spName VARCHAR(128) = OBJECT_NAME(@objId)
     
      DECLARE @evtId INT 
     
      EXEC @evtId = _Admin.dbo.usp_MakeLogEntry
        @srcDb = @dbName
      , @srcSpSchema = @schemaName
      , @srcSpName = @spName
      , @msg = @msg
     
      RETURN @evtId
     
    END

    Note that:

    • You need a copy of usp_LogEvt in any database from which you want to write log entries. This is so that incoming object IDs (@objId) can be resolved correctly to database and object names.1)
    • The EXEC call inside usp_LogEvt is fully qualified, to make sure that you can call it from any database.
    • If your administration database isn't called _Admin, you'll need to change the EXEC call appropriately.
    • You might want to think about creating it in model so that it is automatically added to every new database.

The log entry ID is returned from the call to usp_LogEvt. You don't need to use it at all, but if it is of interest you can do something like:

DECLARE @evtId INT
EXEC @evtId = dbo.usp_LogEvt 'Here is a message', @@PROCID
PRINT @evtId  -- this is the logged message's EventId

1)
For the same reason, don't ever write EXEC DbName.dbo.usp_LogEvt – you want to ensure that you call the local version or your log entries will become meaningless!