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();
}
};
