SQL Server custom EventLog logger
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:
1RAISERROR ('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:
1/*===============================================================================
2 Date: 2012.01.25
3 Description: Custom eventlog logger CLR procedure
4 SQL Server version: 2005 or newer
5 Author: Berke János - IamBerke.com
6---------------------------------------------------------------------------------
7 (cc) 2012, IamBerke.com
8
9You may alter this code for your own *non-commercial* purposes.
10You may republish altered code as long as you give due credit.
11THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
12EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES
13OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
14================================================================================*/
15
16#region Using directive
17using System;
18using System.Data;
19using System.Data.SqlClient;
20using System.Data.SqlTypes;
21using Microsoft.SqlServer.Server;
22using System.Diagnostics;
23#endregion
24
25public partial class StoredProcedures
26{
27 [Microsoft.SqlServer.Server.SqlProcedure]
28
29 public static void LogCustomEvent(string message)
30 {
31
32 //check existence of the SqlEventLog
33 if (!EventLog.Exists("SqlEventLog"))
34 {
35 EventLog.CreateEventSource("SQL Server User Database", "SqlEventLog");
36 }
37
38 EventLog evt = new EventLog("SqlEventLog");
39 evt.Source = "SQL Server User Database";
40
41 //It logs informational messages only
42 evt.WriteEntry(message, EventLogEntryType.Information);
43 }
44};
- Build your solution, then you can use the SQL Script below for deployment:
1/*Make sure you run this query in SQLCMD mode and
2set the path for your dll file*/
3:setvar AssemblyPath '<path>\SqlCustomEventLog.dll'
4
5USE master;
6GO
7--enable CLR runtime
8EXEC sp_configure 'clr enabled', 1;
9RECONFIGURE;
10GO
11--create a test database
12CREATE DATABASE CustomEventLogSampleDb;
13GO
14--we have to enable databse trustworthy for external access of our assembly
15ALTER DATABASE CustomEventLogSampleDb SET TRUSTWORTHY ON;
16GO
17
18USE CustomEventLogSampleDb;
19GO
20
21CREATE ASSEMBLY [SqlCustomEventLog]
22 AUTHORIZATION [dbo]
23 FROM $(AssemblyPath)
24 WITH PERMISSION_SET = EXTERNAL_ACCESS;
25--external access permission set is required for accessing out of sql server context and external resources
26GO
27
28CREATE PROCEDURE [dbo].[LogCustomEvent]
29@message NVARCHAR (4000)
30AS EXTERNAL NAME [SqlCustomEventLog].[StoredProcedures].[LogCustomEvent]
31GO
32
33--sample logging
34DECLARE @message nvarchar(4000) = 'Test EventLog message from ' + DB_NAME();
35EXEC [dbo].[LogCustomEvent] @message = @message;
36GO