An introduction to parametrised queries with DbaTools

Many of us are likely aware of PowerShell, even if we don’t use it too frequently, and I suspect that if you’re reading this post you’re also familar with things like sqlcmd. Hopefully, you have also heard of DbaTools, a module for PowerShell (and if you haven’t, hopefully that’s why you’re here). Today, I wanted to discuss running parametrised queries (including table type parameters) from PowerShell, which is notorious hard/impossible with sqlcmd (or invoke-SqlCmd), using the DbaTools module. Running a basic query Firstly, let’s get the real basics down, and look at connecting to the SQL Server, and running a…

Continue reading

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

Moving to Markdown

Sorry I haven’t posted in awhile. I’ve been working on changing the website to support Markdown, while trying to keep the custom formatting I have added to Prism.js for things like my code blocks (using Cascadia Code font and the additional buttons), and being able to continue to do some more non-standard Markdown stuff like giving my headers an id value so that I can link to the directly. I’ve finally managed to get this mainly working now, where the appearance of new posts won’t look strikingly different. The only real visual changes are that inline code blocks (like this)…

Continue reading