Category Archives: Transact-Sql

Raising Errors in Transact-Sql

There seems to be quite a bit of confusion about the way errors should be raised in newer versions of Transact-Sql. The documentation for RAISERROR says: “New applications should use THROW instead.” It doesn’t say that RAISERROR is deprecated; when you look at the list of deprecated features what you find is that invoking RAISERROR without an error number is now deprecated, but it’s unclear whether the command itself will go. Continue reading Raising Errors in Transact-Sql

JSON Support in Sql Server 2016 CTP2 – Quick Start

Please note: CTP2 has been superseded. See this post for information about JSON in Sql Server CTP3.2

Sql Server CONTEXT_INFO with Numeric Values

A trigger takes no arguments but it is possible to use CONTEXT_INFO() to make 128 bytes of binary data values accessible to it. I wanted to provide an 8-byte (bigint) value from a sequence which the trigger would write to a primary key column.

Storing and retrieving a numeric value in CONTEXT_INFO is not difficult, but it’s another thing that doesn’t come up very often. For this type of problem I like to find concise explanations with source code so that I can get back to the main problem quickly. Once again my searches didn’t turn anything up so I’m trying to fill the gap with this post. Continue reading Sql Server CONTEXT_INFO with Numeric Values

Avoid Running T-Sql Scripts In the Wrong Azure Database

There are problems with "USE" in Sql Azure. If you prefix an SSMS script with “USE {DatabaseName}” to make sure it runs in the right place the statement will either be ignored (you’re already in the database referred to) or raise an error. That will kill the current batch, but if there is a GO statement in the script any statements after it will still run, and of course that will be in the wrong database. What to do? Continue reading Avoid Running T-Sql Scripts In the Wrong Azure Database

Sql Server Identity and Membership Functions

On my current research project I’m looking at security. The number of different functions available (some of which are obsolescent) has always been an irritation, so I decided to create a Transact-Sql query that demonstrates the functions and provides links to the documentation. You can use EXECUTE AS LOGIN or EXECUTE AS USER to explore what’s returned for other security principals, always assuming you have the necessary permissions of course. Hope you find it useful. Continue reading Sql Server Identity and Membership Functions

Agile Continuous Delivery of Databases (3)

A phase of my Agile Blue/Green deployment project is now complete, as I have a Proof of Concept which has been tested successfully. As you may recall from prior posts, the Blue/Green technique features two databases, the “green” live one that users connect to (I’ve made an arbitrary assignment of colours), and the offline “blue” one which has a different version of the schema (a previous or future release). Continue reading Agile Continuous Delivery of Databases (3)