Trap error details if a sql connection fails

Question

I am looking for a better means of error trapping my sql connections on connection.open, so that I may log the error better and determine where in my application it is faulting.

I am using try catch and logging the exception caught but what I really want is to know which connection is failing. My application is iterating through 70 servers and aggregating data for another database.

Answer 1

If you catch the SqlException exception type it should have information specific to the connection attempt. Properties like .Number will return SQL error numbers which should give you some information on why the connection failed.

Answer 2

Just a shot in the dark. If you catch SqlException you need to iterate through its Errors collection. The SqlError objects in the collection have some extra properties including SqlError.Server, which gives exactly what you need.

Gets the name of the instance of SQL Server that generated the error.

There can be more SqlError objects for a single SqlException because the colleciton retruns all the errors the command (sql batch) generated. The first SqlError in the collection corresponds (in error message) with the information in the SqlExpcetion itself, so it is a duplicate in a sense. In my error reporting I preffer to completely ignore the SqlException info and go always after the SqlError info and report that, since is more complete.

Answer 3

{
    ...
    foreach(var connectionString in connectionStrings)
    {
        try
        {
            using (var cnn = new SqlConnection())
            {
                cnn.Open();
            }
        }
        catch (SqlException)
        {
            Log(string.Format("Failed to open connection '{0}'.", connectionString));
        }
    }
    ...
}