-1

My fairly simple stored procedure does this on line 44:

IF @a = @b
    RAISERROR('blah blah blah', 11, 1)
    RETURN

The stored procedure is invoked client-side using the .NET Framework System.Data.SqlClient library:

   try
   {
        SqlCommand c = new SqlCommand();
        c.CommandType = CommandType.StoredProcedure;
        c.CommandText = "procname";
        c.ExecuteNonQuery()   // execute the stored procedure 
   }
   catch(SqlException sex)
       throw sex;
   catch(Exception ex)
   {
        throw ex;
   }

When ex is caught, its value is blah blah blah + CRLF + 1259

Where is that 1259 coming from? Does it correspond to severity 11?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim
  • 8,669
  • 31
  • 105
  • 183

1 Answers1

1

Aha! Just found it. There's a PRINT statement some lines above the RAISERROR. Didn't know that (unrelated) PRINT statements get appended to the error message!

  PRINT 'Fee fie fo fum'

  <snip>

  if @a = @b
     RAISERROR('blah blah blah', 11, 1)
     return

Client-side the SqlException Message property is "blah blah blah" + CRLF + "Fee fie fo fum"

Tim
  • 8,669
  • 31
  • 105
  • 183
  • *" Didn't know that (unrelated) PRINT statements get appended to the error message!"* They don't. – Thom A Oct 30 '20 at 19:12
  • They do. I changed it to "PRINT 'Fee fie fo fum' " and now my error message client-side reads `blah blah blah` + CRLF + `fee fie fo fum`. Must be a`System.Data.SqlClient` feature. – Tim Oct 30 '20 at 19:13
  • Same behavior with `SqlException`. – Tim Oct 30 '20 at 19:18
  • 2
    The database engine returns all messages generated by `PRINT`, `RAISERROR`, `THROW` along with informational, warning, error, and API messages back to the client. The differentiator is that errors with severity 11 or greater (`SqlError.Class` >= 11) will also throw an exception with SqlClient and most other APIs. The `SqlException.Errors` collection will contain all the messages, though, so you'll need to filter on `Class` to discard the informational and warning messages. – Dan Guzman Oct 31 '20 at 10:02
  • Thank you @Dan Guzman. I had never encountered this behavior with PRINT and RAISERROR and System.Data.SqlClient before. – Tim Oct 31 '20 at 12:09