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

Views: 896
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 legal example for this post, is here in the company where I work, where there is a service that monitors the Event Viewer on 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 that works on a 24 × 7 basis makes the telephone call for 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 certain 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 by SQL Server without using the CLR? It is, although it is not so "elegant", since it is not possible to specify the source (Source) of the event.

One way to do this is to use the 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.