none
SqlException vs generic Exception - getting more details from sql error RRS feed

  • Question

  • I'm using ADO to fire off an update statement.  Sometimes I catch an exception that includes a message such as

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    I suspect that something has already locked the table im trying to update.  The above message isnt really enough to tell where the problem is.  I would think that if I had ran the update statement in sql management studio, I would have received a more helpful error message, or at least got the process id that has the table locked.

    However, my current exception handling is only capturing a generic exception, and then logging the stack trace and the exception message, not the whole exception.  Also, Im thinking I need to first catch a sqlexception, if that is what is happening.  As in this link

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.aspx

    The above link has some sample code

    public static void ShowSqlException(string connectionString)
    {
        string queryString = "EXECUTE NonExistantStoredProcedure";
    
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            try
            {
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                DisplaySqlErrors(ex);
            }
        }
    }
    
    private static void DisplaySqlErrors(SqlException exception)
    {
        for (int i = 0; i < exception.Errors.Count; i++)
        {
            Console.WriteLine("Index #" + i + "\n" +
                "Error: " + exception.Errors[i].ToString() + "\n");
        }
        Console.ReadLine();
    }

    Will this give me the additional details that I would see if I executed the update in management studio?

    Also, what can I do to find out what could have a lock on the table - at the time the error happens?  If I caught it when I had time to go look, I could probably find it in sql, but by the time I get to looking at it, the lock is probably already released, as the process that had the lock is already done.

    suggestions?

    Tuesday, July 2, 2013 12:14 PM

Answers

  • You are unlikely to get any additional information from the exception.  SQL timing out a query is different than ADO.NET timing out.  If ADO.NET doesn't get a response back within a reasonable amount of time it throws this exception.  The exception isn't tied to SQL at all (it might not have even got to the DB yet).  Whether that is because of a SQL error, delay in the network, whatever isn't relevant. 

    SQL isn't going to time out a query but SSMS might (it is configurable how long you wait).  If you have a query that is taking too long then the best way to debug it is to profile it in SSMS.  From there you can see the execution plan and also look to see if any other process has a lock on a table you're using.  Debugging long queries isn't the job of the VS debugger or your code.  That is what SSMS is for.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Tuesday, July 2, 2013 4:46 PM
    Moderator