Error Handling Final Comments
Today I’m Wrapping up this series on error handling with some interesting insights from two of our readers. One is from an SQL Server perspective, and the other reflects code in all layers.
Fraser Responds to Error Handling flow with the following thoughts:
> Start at the highest level of code when handling errors.
Personally I often start at the most local context since that’s where the greatest understanding of what was being attempted at that point and therefore the information that can be logged and/or propagated is likely to be more meaningful. Simply allowing exceptions to bubble up to the highest level seems a bit of an anti-pattern to me.
> When an error occurs in an application process initiated by an end
> user, errors are best handled in a friendly fashion.
Agreed, but again the emphasis for me would be to maintain appropriate layering in the application such that exceptions in non UI layers aren’t expected to provide text or other UI specific details that are for the specific purpose of presentation. Typically I would raise an error code which conveys sufficient meaning to the caller to understand whether it’s worth continuing or not. As for screen text, the caller should take care of that possibly based on a transliteration of the code they have been passed or not. Either way maintaining clear boundaries is worth IMHO. Of course YMMV 🙂
Marice shares some tricks for handling errors in SQL Server resulting from operating system issues:
The new Try / Catch block of T-SQL is great but lacks the ability to catch all T-SQL error that messages that have a OS Level cause.
For example when someone runs the backup database command with an invalid directory like this.
backup database cdd to disk='c:invalidDestinationccc.bak'
Both messages are returned
Msg 3201, Niveau 16, État 1, Ligne 1
Cannot open backup device 'c:invalidDestinationccc.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15100).
Msg 3013, Niveau 16, État 1, Ligne 1
BACKUP DATABASE is terminating abnormally.
But the Try/Catch block only gets the last message, which is much less meaningful than the first one.
In the free maintenance tool script YourSqlDba, I put a workaround for this problem.
I wrote a CLR Stored procedure to run T-SQL code. DotNet client is able to handle this if you provide an inline delegate to handle messages.
The only problem to this is that the order of messages is not guaranteed in a batch of more than one T-SQL statement.
For those who are interested, the corresponding C# code is put into comment in the script under the name ExecuteYourSqlDbaCmdsThroughCLR.
You can download the script from www.grics.qc.ca/YourSqlDba.
It is imbedded into the Html Help documentation. There is a link in the documentation home page to open it.
That’s all for this week. Drop me a note with any comments at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Encryption in SQL Server 2008
In the past, it was necessary to either use a third-party tool, or if you were using cell-based encryption you would need to change the data type of the column to varbinary. This would, on occasion, require changes to foreign keys and make searching more complex. The application needed to make an additional call to encrypt and decrypt the data. Transparent data encryption will allow you to apply encryption to a database without having to change the application that accesses the database. All data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk when using transparent data encryption.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008
Written by Townsend Security
Simplify encryption and key management on … (read more)