Home Posts tagged "errors"

Tag: errors

Stop using ROLLBACK in triggers; THROW an error

A problem I’ve seen raised on numerous occasions is users that are getting a non-descript error when they are doing some kind of DML/DDL operation, and they don’t know why they are getting the error, and what is causing it. That error is: Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted. So, for the "unsuspecting" user that’s performing an INSERT or maybe an ALTER statement, all they know is that the statement failed in a trigger. There are no details what what trigger caused the error, and no details…

Continue reading

Getting the line sys.sp_executesql was executed on during an error

This is a problem I encountered recently on a question on Stack Overflow but found it interesting enough that I wanted to also share it here. When you are using dynamic SQL, you may have noticed that when an error occurs the line number provided in the error is that of the query in the dynamic batch, not the outer query. Take for example the following example: DECLARE @SQL nvarchar(MAX), @CRLF nchar(2) = NCHAR(13) + NCHAR(10); SET @SQL = N'DECLARE @name sysname;' + @CRLF + N'SELECT @name = name' + @CRLF + N'FROM sys.tables' + @CRLF + N'WHERE object_id =…

Continue reading