How to get call stack info in SQL Server's catch block?
When I have multiple errors in my query, and I use
error_message()function in my catch block, I only get the last error message.
For example, if my errors are:
Msg 2714, Level 16, State 5, Line 14 There is already an object named 'IX_EntityTypeConfigs' in the database. Msg 1750, Level 16, State 1, Line 14 Could not create constraint or index. See previous errors.
in the catch block I just get:
Could not create constraint or index. See previous errors.
How can I get the whole message?
I have searched and saw that this is a limitation of SQL Server, but articles belonged to 2014 and older than that.
I wonder if things has changed since.
I believe it's still correct. The recommended way is to rethrow the errors to the application via the
THROWcommand and let the app handle it.
The best resource is still this article by https://www.sommarskog.se/error_handling/Part1.html
You can also capture the errors with Extended Events (XE)
error_reported(on SQL Server side). That would show you all the errors that were fired.
I blog about it here: https://straightforwardsql.com/posts/investigating-errors-with-extended-events/ .