Error handling CLR routines

Question

How do you raise a SQL Server error event (similar to RAISERROR) from within a SQL CLR routine?

Answer 1

In theory, based on a code sample I got from one of our devs:

try
{
    response = service.ProcessThisInfo(info);
}
catch (Exception ex)
{
      returnCode = Convert.ToInt32(ErrorEnum.SomeError);
      string msg = string.Format("RAISERROR('{0}',16,1)", ex.Message.Replace('\'', ' '));
      SqlCommand cmd = new SqlCommand(msg, connection);
      try
      {
            SqlContext.Pipe.ExecuteAndSend(cmd);
      }
      catch { } // stop duplicate exception throwing...
}

In practise, maybe not It's an issue in sql 2005

Answer 2

I think you just get a nasty error if you try this. There is an interesting sample for the SqlContext.Pipe.ExecuteAndSend(cmd) on MSDN at http://msdn.microsoft.com/en-us/library/ms255336(VS.80).aspx It doesn't work without modification, and if you change it to sent a raiserror, it goes dolally and sends you back a NET Exception. You can't do it with a function.

As far as the original question goes, I'd say it (raising a SQL Server error from within a CLR function) was impossible in SQL Server 2005, but can be done from a CLR procedure.