Home Posts tagged "CC BY-ND"

Tag: CC BY-ND

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

sql_variant parameters and Dynamic SQL

Let’s start off by saying that this article is not a recommendation to use sql_variant as a data type. There are many articles, written by far more reputable people, that have explained why sql_variant isn’t a good choice of a data type. Saying that, however, if you are using dynamic SQL then yes you could pass one as a parameter to have that dynamic statement correctly cast that parameter to the correct data type. For a "catch all" query, where you are passing a both a dynamic column and value that could have different data types this permits you to…

Continue reading

An in-depth look at injecting

Injecting into SQL is something I have covered multiple times, but today I wanted to cover it a bit more in full to touch on why doing it incorrectly is a problem, and also the different ways to inject properly. Some of this will definitely be repetition of stuff I’ve said before, but having I felt that having it in one article isn’t a bad thing. When SQL Injection goes wrong The biggest problem with injecting is doing it incorrectly, and thus opening your instance up to SQL injection attacks. For those of you that you that don’t know what…

Continue reading

A CURSOR free version of sp_msforeachdb

Something that we are all keenly aware of is that (generally) the use of a CURSOR in T-SQL is generally bad. A CURSOR is an iterative based method, and these generally perform very poorly in SQL Server, which is far better suited to set-based methods. sp_msforeachdb is an undocumented Special Procedure, so one day it may be removed (unannounced), but it also uses a CURSOR and can be unreliable. Something, like Aaron Bertrand did with sp_foreachdb, I have designed my own with some extra features. The Objects Let’s get to the interesting part first, the Objects. There are 2 parts…

Continue reading