Sql-server – How to get all messages raised when a statement fails

error handlingerror logsql serversql-server-2008

Executing the below query will generate two error messages 

ALTER TABLE [MFClassProperty] DROP CONSTRAINT [FK_MFClassProperty_MFProperty]

Error messages are:

Msg 3728, Level 16, State 1, Line 3 'FK_MFClassProperty_MFProperty' is
not a constraint. Msg 3727, Level 16, State 0, Line 3 Could not drop
constraint. See previous errors.

If I use a try..catch block, then it shows only the last message.

  • Why isn't it moving to the catch block on the first error?
  • Is there any way to get all error messages?

This is sample situation.

Best Answer

This is expected behavior with TRY/CATCH in T-SQL. The related Connect bug report was closed as "won't fix":

You'll need to use a client application (e.g. SQLCMD, PowerShell) to capture all error messages.

Related Question