At first, I was excited to read about the new TRY...CATCH exception handling in SQL Server 2005, but it feels like it has a serious flaw: only the last error message is returned in the Exception object!
This is really annoying, both during development and in production. For example, if you make a mistake while trying to create a CONSTRAINT:
-- Setup. IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblInvoice') DROP TABLE tblInvoice IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblCustomer') DROP TABLE tblCustomer CREATE TABLE tblCustomer ( CustomerID INT, ) CREATE TABLE tblInvoice ( InvoiceID INT, CustomerID INT, )
Under SQL Server 2000, we get complete error information:
ALTER TABLE tblInvoice ADD CONSTRAINT FK_tblInvoice_tblCustomer FOREIGN KEY (CustomerID) REFERENCES dbo.tblCustomer(CustomerIDDDDD) -- Error! Msg 1770, Level 16, State 0, Line 18 Foreign key 'FK_tblInvoice_tblCustomer' references invalid column 'CustomerIDDDDD' in referenced table 'dbo.tblCustomer'. Msg 1750, Level 16, State 0, Line 18 Could not create constraint. See previous errors.
But under SQL Server 2005's exception mechanism, the error message for this code is almost useless:
BEGIN TRY ALTER TABLE tblInvoice ADD CONSTRAINT FK_tblInvoice_tblCustomer FOREIGN KEY (CustomerID) REFERENCES tblCustomer (CustomerIDDDDD) END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH Msg 50000, Level 16, State 42, Line 24 Could not create constraint. See previous errors.
Microsoft's NaveenP explains ( http://blogs.msdn.com/sqlprogrammability/archive/2006/04/03/567550.aspx ):
"When an error is raised in a some special context, the first message has information about the error, while subsequent messages provides information about the context. This becomes an issue inside tsql try-catch. In the absence of any notion about error collection, the catch block is activated with one of the error messages. As Sql Server 2000 would have set @@error to the last error message, we decided to set error intrinsics (error_message(), etc.) to the last error message inside the catch block. In other words Sql Server 2005 would ignore all but the last error message inside [the] tsql try-catch... Backward compatibility prevents us from mergeing old error messages."