Is it possible that SqlException is thrown and property Number == 0?

Question

Is it possible that SqlException will be thrown when trying to execute sql procedure and property Number will be 0?

Specific situation: domain controller is not available/not responding and no real communication with sql server occurred.

check this:https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.number(v=vs.110).aspx — TheGameiswar, Jan 05 '17 at 10:43
and this :https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.number(v=vs.110).aspx — TheGameiswar, Jan 05 '17 at 10:44
do you mean interrupted connectivity when a SqlConnection has already been opened before? The client components would in this case still throw an "A transport-level error has occurred..." message, an that exception does have a number (19 - Physical connection is not usable). There *are* defined error states with number == 0 though. — Cee McSharpface, Jan 05 '17 at 10:44
Not only is this possible, there are entire classes of `SqlException` instances that get thrown without SQL Server ever being involved, and it's not even particularly easy to know when this has happened. Error numbers are not consistently assigned; sometimes you get an SQL error number, sometimes a Win32 error number. — Jeroen Mostert, Jan 05 '17 at 11:02
@SouvikGhosh Active Directory domain controller was not working - i don`t have all the details except that disk was full. — Tomasz Cyborowski, Jan 05 '17 at 12:07
@dlatikay I suspect that it could have happen when trying to open connection. — Tomasz Cyborowski, Jan 05 '17 at 12:12

score 1 · Answer 1 · answered Jan 05 '17 at 11:18

No, it should not be possible that SqlException.Number is 0, except for the following cases:

Read-only routing failure
Server had severe error processing query
Processed cancellation while parsing results
Failed to create user instance

Assuming you're not using read-only routing of SQL Server 2016, then none of these cases could apply to the scenario you described.

Remaining cases are either a non-zero number from sysmessages, or a Win32 error code (which, since 0 == ERROR_SUCCESS, would also never be zero).

In practice you will see something along the lines of "Cannot create SSPI context", or "A transport-level error has occurred when sending the request to the server", or a "An existing connection was forcibly closed by the remote host", and those have nonzero Win32 error codes too.

Reference: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.number(v=vs.110).aspx

It may be worth noting that SqlException.Number is synonym for the Number property of the first entry in its Errors array, and that array might contain more than one item.

score 1 · Answer 2 · answered Jan 17 '17 at 08:00

Answering my own question. Yes, it is possible. We added aditional code to catch:

catch (SqlException ex)
{
    for (int i = 0; i < ex.Errors.Count; i++)
    {
        this.logger.Error("Index #" + i + "\n" +
        "Message: " + ex.Errors[i].Message + "\n" +
        "Error Number: " + ex.Errors[i].Number + "\n" +
        "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
        "Source: " + ex.Errors[i].Source + "\n" +
        "Procedure: " + ex.Errors[i].Procedure + "\n");
    }                       

    if (ex.Number == 0)
    {
        this.logger.Warn("Exception was caught but ex.Number == 0! Changing to -69.");
        oResults.Add("_ExitCode", "-69");
    }
}

and this was logged today.

2017-01-17 08:05:08,282 [r074008903] ERROR ProcessLogger Index #0
Message: A severe error occurred on the current command.  The results, if any, should be discarded.
Error Number: 0
LineNumber: 0
Source: .Net SqlClient Data Provider
Procedure: 

2017-01-17 08:05:08,282 [r074008903] ERROR ProcessLogger Index #1
Message: A severe error occurred on the current command.  The results, if any, should be discarded.
Error Number: 0
LineNumber: 0
Source: .Net SqlClient Data Provider
Procedure: 

2017-01-17 08:05:08,283 [r074008903] WARN  ProcessLogger Exception was caught but ex.Number == 0! Changing to -69.

Because in other place _ExitCode was used to determine is something was wrong in this specific situation we had false information.

score 1 · Answer 3 · answered Jun 16 '20 at 00:17

It is possible.

I receive 0 when trying to connect SQL which is starting inside ubuntu container. According to this: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15 that is undocumented and, probably, is a bug.

Here is my situation, for example:

Is it possible that SqlException is thrown and property Number == 0?

3 Answers3