I am Berke

I’m active on several forums (TechNet, MSDN, etc.) and found some interesting questions, I’d like to blog about. One of the topic is a custom event logging module for SQL Server.

I can use a built in function to log any error to the Application Event Log with the following code snippet:

RAISERROR ('Error sample', 16,1) WITH LOG;

Ok, this works if I want to use Application log. What can I do if I have to have my custom Event Log? If I have SQL Server 2005 or later, obvious answer is CLR Stored Procedure. Follow the steps below to create your project:

• Start Visual Studio, then click File, New, Project
• Select Database, SQL Server, Visual C# Database Project from the installed templates. If you do not have this project type, you must install SQL Server client tools.
• Right click on the project and select Add, Stored Procedure and name it LogCustomEvent.cs
• Copy the following code into the cs file:

/*=============================================================================== 
  Date: 2012.01.25
  Description: Custom eventlog logger CLR procedure
  SQL Server version: 2005 or newer 
  Author: Berke János -  IamBerke.com 
--------------------------------------------------------------------------------- 
  (cc) 2012, IamBerke.com 
   
You may alter this code for your own *non-commercial* purposes.  
You may republish altered code as long as you give due credit. 
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,  
EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES  
OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. 
================================================================================*/ 
 
#region Using directive
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
#endregion 

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void LogCustomEvent(string message)
    {
        
        //check existence of the SqlEventLog
        if (!EventLog.Exists("SqlEventLog"))
        {
            EventLog.CreateEventSource("SQL Server User Database", "SqlEventLog");
        }

        EventLog evt = new EventLog("SqlEventLog");
        evt.Source = "SQL Server User Database";

        //It logs informational messages only
        evt.WriteEntry(message, EventLogEntryType.Information);
    }
};

• Build your solution, then you can use the SQL Script below for deployment:

/*Make sure you run this query in SQLCMD mode and 
set the path for your dll file*/
:setvar AssemblyPath  '<path>\SqlCustomEventLog.dll'

USE master;
GO
--enable CLR runtime
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
--create a test database
CREATE DATABASE CustomEventLogSampleDb;
GO
--we have to enable databse trustworthy for external access of our assembly
ALTER DATABASE CustomEventLogSampleDb SET TRUSTWORTHY ON;
GO

USE CustomEventLogSampleDb;
GO

CREATE ASSEMBLY [SqlCustomEventLog]
    AUTHORIZATION [dbo]
    FROM $(AssemblyPath)
    WITH PERMISSION_SET = EXTERNAL_ACCESS; 
--external access permission set is required for accessing out of sql server context and external resources
GO

CREATE PROCEDURE [dbo].[LogCustomEvent]
@message NVARCHAR (4000)
AS EXTERNAL NAME [SqlCustomEventLog].[StoredProcedures].[LogCustomEvent]
GO

--sample logging
DECLARE @message nvarchar(4000) = 'Test EventLog message from ' + DB_NAME();
EXEC [dbo].[LogCustomEvent] @message = @message;
GO