How to send logs to GrayLog from T-SQL procedures?

Question

I can send logs to log collectors from C# applications using log4net+GELF appender.

But how to send logs to GrayLog from T-SQL procedures? There are code:

1. WinForms app works fine, I run it on the same machine where SQL Server installed. I see all logs received in GrayLog:

   private void button1_Click(object sender, EventArgs e) { string facility = "DoBeDo"; string host = "my-host-name"; int port = 12201;

            try
            {
                using (var logger = new GrayLogUdpClient(facility, host, port))
                {
                    logger.Send("Hello", "Jonny Holiday", new { Username = "John", Email = "[email protected]" });
                }
            }
            catch(Exception xx)
            {
                Console.WriteLine("***Exception:{0}", xx.Message);
            }
        }
   

2. There is SQLCLR code, it works, I see messages in SSMS but no any records in GrayLog and no any exceptions:

   public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SqlSPHelper(SqlString msg) { try { SqlContext.Pipe.Send(@"SqlSPHelper:: Start"); string facility = "DoBeDo"; string host = "my-host-name"; int port = 12201;

            try
            {
                using (var logger = new GrayLogUdpClient(facility, host, port))
                {
                    logger.Send("Hey", "Donny Hooligan", new { Username = "Donald", Email = "[email protected]" });
                }
            }
            catch (Exception xx)
            {
                Console.WriteLine("***Exception:{0}", xx.Message);
            }
        }
        catch(Exception xx)
        {
            SqlContext.Pipe.Send("1:"+xx.Message);
        }
        SqlContext.Pipe.Send(@"SqlSPHelper:: Completed");
    }
   

What is wrong? How to send logs to GrayLog?

Answer 1

I think you can do this in couple of ways:

1. Define a CLR stored procedure, which does the logging part
2. In the CATCH Block of the stored procedure, RAISERROR WITH LOG to write to Windows event viewer & SQL Server error log. Later you can filter and read these events from event viewer in your GrayLog.

The first method is better, as it is cleaner. Second method makes the SQL Server error log to have many log entries, which would cause false alarms for DBA team.