Using a CLR TVF to SELECT from the SQL Error Log

One of the things I have always wanted to do, but have never been able to easily is to select from the ErrorLogs as if they were tables. To do this in the past has always required creating a temp table, inserting the results of the xp_readerrorlog or sp_readerrorlog into it, and then selecting out of that table. By using a CLR TVF with EXTERNAL_ACCESS, you can easily be able to select from the ErrorLogs as if they were actual tables in a simple statement.

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

/*=========================================================================================

  File:      fn_ReadErrorLog.cs

  Summary:   Table-valued function to retrieve the SQL Server Error Log for the

             current instance as a table.

  Parameter: LogNumber - The Log Number in the ErrorLogs to Read.

             Specify NULL or 0 to refer to active log

  Date:      May 9, 2008

---------------------------------------------------------------------

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.

============================================================================================ */

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction(Name="fn_ReadErrorLog", 

        FillRowMethodName = "FillRowErrorLogReader", 

        DataAccess = DataAccessKind.Read, 

        TableDefinition = "LogDate datetime, ProcessInfo nvarchar(100), Text nvarchar(4000)")]

    public static IEnumerable InitMethodErrorLogReader(int LogNumber)

    {

        List<string[]> logitems = new List<string[]>();

        string servername;

        using (SqlConnection conn = new SqlConnection("context connection=true"))

        {

            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandText = "select serverproperty('ServerName')";

                servername = (string)cmd.ExecuteScalar();

            }

            conn.Close();

        }

        using (SqlConnection connection = new SqlConnection("Server=" + servername + ";Integrated Security=true"))

        {

            connection.Open();

            SqlCommand sqlCommand = connection.CreateCommand();

            sqlCommand.CommandText = "exec xp_readerrorlog " +LogNumber.ToString();

            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

            while (sqlDataReader.Read())

            {

                string[] item = new string[3];

                item.SetValue(sqlDataReader["LogDate"].ToString(), 0);

                item.SetValue(sqlDataReader["ProcessInfo"].ToString(), 1);

                item.SetValue(sqlDataReader["Text"].ToString(), 2);

                logitems.Add(item);

            }

        }

        return logitems;

    }

    public static void FillRowErrorLogReader(object obj, out SqlDateTime logDate,

        out SqlString processInfo, out SqlString text)

    {

        string[] item = (string[])obj;

        logDate = (SqlDateTime)DateTime.Parse(item[0].ToString());

        processInfo = (SqlString)item[1].ToString();

        text = (SqlString)item[2].ToString();

    }

};

This Example will also be posted on SQLCLR.net in the coming days.