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.

Using Parameters With Dynamic T-Sql

It’s a simple technique but I haven’t often used it. The documentation was a bit disappointing, so this is an aide-memoire which (I hope) makes things simpler. The stored procedure sp_executesql does the job.

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?

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.

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).