Make is possible to retrieve all error messages in a CATCH handler
When you trap an error in a CATCH handler, you can use the functions error_message, error_number etc to get information about the error. This works well, as long as only one error message is produced. However, there are situations where multiple error message. In this case you can only retrieve one error message, and with a bit of bad luck this is only a generic error message. A typical example is BACKUP-RESTORE:
BACKUP DATABASE msdb TO DISK = 'X:\nosuchdisk\dxx.bak'
The output is "BACKUP DATABASE is terminating abnormally." The message that path does not exist is lost.
In the long run, this is not satisfactory, and you should have a better story.
Tom Zhao commented
we want error messages!
Michael Søndergaard commented
This is really annoying, that you cannot get all inner exceptions in Transact SQL, and have to leave transact sql code to get all messages.
thank you Mr.Sommarskog, this is exactly what's happening and will be very helpful for my project if it can be fixed. it's a shame there are not many votes