SQL Try and Catch masking errors

Question

I have a stored procedure which is doing an insert into a table from a stored procedure.

create procedure spImportData
as
begin

    begin try

    insert into table (id, notes)
    exec linkedserver.database.dbo.spGetData

    end try

    begin catch

    select error_number(), error_state(), error_message()

    end catch

end

What I found is this, if I run the stored procedure, it returns an error message

Msg 0, Level 11, State 0, Line0
A severe error occurred on the current command. The results, if any should be discarded.

If I however remove the try and catch blocks. it reveals the actual error which in this case is a truncation error.

Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.

I am wondering why is the try and catch not returning the correct meaningful error message

SQL version is SQL 2014.

Answer 1

Error handling is not SQL Server's best game. And it only gets worse if you add linked servers to the mix. I have a series of articles on error handling on my web site, and there an appendix that covers linked servers, https://www.sommarskog.se/error_handling/Appendix1.html. It's quite scary.

When you see this particular error message, A severe error occurred on the current command. The results, if any should be discarded., this indicates that there has been an internal error of some sort. If the severity level is 20, it was an internal error in SQL Server, and your connection was cut. You would also find an error message in the SQL Server error log.

In you case, the severity level is 11, which indicates that the error occurred in SqlClient, the client API that SSMS uses. However, I believe that most of the time you see this error, SQL Server is sending illegal TDS.