SQL Server - How to read and write events in Windows Event Viewer using CLR (C #)

Views: 763
Reading Time: 5 minutes

Hello people
Alright?

In this post, I would like to show you how to read and write events in the Windows Event Viewer using CLR (C #). To learn more about CLR, this powerful feature that lets you create and execute code written using the C # programming language and the .NET Framework to extend SQL Server functionality, visit the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.

In many situations, having this information available for database queries can be very useful for creating internal monitoring or for creating web applications that consume this information and help the development team identify application and service issues, as well as allow them to can write custom event messages within Stored Procedures of the SQL Server database, according to certain situations.

Another nice example for this post is here in the company I work for, where there is a service that keeps monitoring Event Viewer from a server, looking for an event where the application is “XPTO” and the type is “Error” . When an event is generated using this application, a monitoring team working on the 24 × 7 regime calls the phone to a certain number to take the necessary actions, especially for highly critical routines that cannot fail or stop. This event is recorded by the CLR when a particular situation in the database occurs.

How to record events in Event Viewer through SQL Server

To write events to the Windows Event Viewer from SQL Server, simply run the StpEvent_Viewer_Store Procedure from your CLR library.

Parameters:

  • @Ds_Server: Name of the server you want to record the event.
  • @Ds_EventType: Type of event you want to record (Error, FailureAudit, Warning, SuccessAudit, Success or Information)
  • @Ds_Fonte: Name you want to set as the event source (Usually, the system or application name)
  • @Ds_Message: Message you want to write to the event
  • @Id_Evento: Code of the error or message you want to generate for this event (free choice of yours)

View source

Example of use

How to record events in Event Viewer through SQL Server (No CLR)

Is it possible to record events in Event Viewer through SQL Server without using CLR? Yes, although it is not so “elegant” as it is not possible to specify the source of the event.

One way to do this is by using SQL Agent's “Write to the Windows Application event log” option:

The other way is by using the undocumented xp_logevent procedure, which I had even published about it in the post The undocumented SQL Server extended procedures:

Example of use:

How to read events in Event Viewer from SQL Server

To read events in the Windows Event Viewer from SQL Server, I will share two ways to accomplish this goal, one using a Stored Procedure and one using a Table-valued function, which gives us more flexibility to work with the data.

View stpEvent_Viewer_Listar source

View fncEvent_Viewer_Listar source code

Usage examples

Listing events using SP

Listing events using the function

That's it folks!
I hope you enjoyed this post and please do not confuse Event Viewer events with SQL Server log events .. rs
Regards and see you next post.