Why does a SqlException thrown by SqlCommand.ExecuteNonQuery contain all the PRINTs as errors?

Question

Hello!

When I run the following snippet

try
{
 using (SqlConnection conn = new SqlConnection("I'm shy"))
 {
  conn.Open();

  using (SqlCommand cmd = conn.CreateCommand())
  {
   cmd.CommandText = "PRINT 'A';PRINT 'B';PRINT 'C';RAISERROR('SQL_Error', 18, 1)";
   cmd.ExecuteNonQuery();
  }
 }
}
catch (SqlException ex)
{
 MessageBox.Show(ex.Message);
}

I get the following message:

SQL_Error
A
B
C

and ex.Errors has 4 entries (The 3 SqlError's corresponding to the prints have a SqlError.Class of 0 (vs. 18 for the real error)

However, if I replace ExecuteNonQuery with ExecuteScalar, I get the expected result:

The message is SQL_Error and I only have one entry in ex.Errors...

Is there any way to avoid the strange behavior of cmd.ExecuteNonQuery??

Answer 1

ExecuteNonQuery normally returns a recordset while ExecuteScalar returns the first row + first column.

Answer 2

No you can't avoid this behavior. Its the result of the way TdsParser.ThrowExceptionAndWarning() is written

particularly this line

  bool breakConnection = this.AddSqlErrorToCollection(ref temp, ref this._errors) | this.AddSqlErrorToCollection(ref temp, ref this._attentionErrors);
        breakConnection |= this.AddSqlErrorToCollection(ref temp, ref this._warnings);
        breakConnection |= this.AddSqlErrorToCollection(ref temp, ref this._attentionWarnings);

My guess is that for whatever reason one of the collection _error or _attentionErrors is empty for ExecuteScaler and its not for ExecuteNonQuery.

I'm sure if you poked around enough you could probably find out why.

In any case you seem to have the workaround already. Only use the first item in SQLExecption.Error