Archive for July, 2009

SQL Server TRY-CATCH error handling

Wednesday, July 22nd, 2009

Thanks to four very talented and inquisitive developers who were taking my Implementing a Microsoft SQL Server 2005 Database class last week, I was encouraged to delve a little deeper into SQL Server’s TRY-CATCH error handling structure.  (You know who you are, and thank you….)

First implemented in SQL Server 2005, the idea was to introduce structured exception and error handling into SQL Server.  The general idea goes something like this:

BEGIN TRY
      <SQL Statement(s) to attempt data modification go here>
END TRY
BEGIN CATCH
      <SQL Statements for Custom Error Handling Go Here>
END CATCH

If the code contained between the BEGIN TRY and END TRY statements runs successfully, then execution jumps to the next statement after the END CATCH statement.  If the code contained between the BEGIN TRY and END TRY statements produces an error with a severity level greater than 10 that are not fatal to the connection.

If you are familiar with the TRY-CATCH block in other Microsoft languages such as Visual Basic, you are used to seeing execution jump to the code contained in the CATCH block immediately after the error (exception) is thrown.  Unfortunately for consistency’s sake, this is not the case for SQL Server.

In SQL Server 2005 and SQL Server 2008, all code between the BEGIN TRY and END TRY executes before the error states are checked.  This means you can get yourself into trouble if you “assume” that it behaves like the TRY-CATCH block in other languages.  For example:

BEGIN TRY
     BEGIN TRAN
          <SQL Statement(s) to attempt data modification go here>
     COMMIT TRAN
     BEGIN TRAN
          <SQL Statements(s) to attempt a second data modification go here>
     COMMIT TRAN
END TRY
BEGIN CATCH
      <SQL Statements for Custom Error Handling Go Here>
END CATCH

If SQL Server’s TRY-CATCH behaved like Microsoft’s complied languages, then if the first transaction failed, execution would immediately jump to the CATCH block.  However, in SQL Server, both transactions would run, and while a failure of either would cause the CATCH block to execute, you could end up with one transaction committed you did not expect to be committed.

While SQL Server’s version of the TRY-CATCH is restricted to a single batch, it is not restricted to a single transaction.  If you are going to use the TRY-CATCH in SQL Server, you will want to make sure you only have one transaction or logical unit of work in the TRY block.

 Hope you find this useful;

 James

Book Review–Windows Powershell Scripting Guide

Thursday, July 9th, 2009

 It’s not often I give a book rave reviews, but I only have one small nitpicky complaint about the Windows Powershell Scripting Guide.  That is that the title doesn’t truely convey the usefulness of this book for Windows administrators.

A better title, in my humble opinion, would be:  Powershell Scripts That Do Everything In Windows.

 The first two chapters give a nice basic overview of the Powershell environment, its configuration, and basic language elements (flow control, conditionals, etc.).

 After that, all the rest of the book is about scripts to accomplish many of the day to day Windows administration tasks in Powershell.  Need to read event logs, manage your failover cluster, or configure Server Core?  Scripts to accomplish those tasks and many more are in this book.

 If you want or need to script in Windows, you should have this book for the amazing number of scripts the author includes, if nothing else.

 Thanks;

James

Book Review–Deploying Messaging Solutions with Microsoft Exchange Server 2007

Friday, July 3rd, 2009

Well, this book is designed to prepare you for Microsoft Exam 70-238.  In that regard, I give the book a 50% success rate at best.

 While the fundamentals of the book are good, there are a number of content areas on the exam that the book either gives no information about or very limited information.  If you are new to Exchange Server 2007 and want to use this book, it provides a good starting point, but DO NOT RELY ON IT FOR YOUR ENTIRE PREPARATION FOR THE EXAM!!!!

If you do, well, e-mail me and we’ll place a side wager on whether or not you pass your exam.

I could not review the included practice tests on the CD because my CD was defective and would not play on either of my computers.  I also found the Case Scenarios wordy and lacking truly technical questions like those on the exam.  Some of the Suggested Practices for hands-on work were very weak as well.

In summary, if you are new to Exchange Server 2007, this book has some good background information, but it is not a comprehensive review or learning resource for passing your exam.

James