question

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 asked ErlandSommarskog answered

SQL Try and Catch masking errors

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.




sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.