T-SQL: Use GO to run the same batch more than once

A simple post this one, but something I only became aware of in the past few months. One of our devs hadn't seen it either so i thought i'd pop it down on this blog.

Consider a scenario where you are generating test data, perhaps with GETDATE() and you want to quickly generate lots of rows. By simply adding a number after the GO batch separator you can get you batch to execute multiple times. No need to write any crazy loops or any joins.

So, something like the following will generate 55 rows:

CREATE TABLE t (id IDENTITY(1,1), dt DATETIME)
GO;
INSERT INTO t SELECT GETDATE()
GO 55;

Simple, but effective.

Powershell: Check for installed Hotfixes

Just found this cool powershell method for finding whether a hotfix has been applied to a computer. I was having an issue with my VMWare VSphere Client - a known issue with a particular hotfix released by MS being the culprit. I have plenty things installed on my machine and it takes Control Panel forever to load the list of updates, and even then its a slow process locating the one.

Powershell has a quick method for locating the KB. Simply run the following:

Get-Hotfix -Id KB980773

This will throw an error if it doesn't exist, but thats ok for my purposes. I could see this being a neat way of checking a farm of servers for a particular update.

T-SQL: Beware the hardcoded switch

This months T-SQL Tuesday is brought to us by Adam Machanic (Blog|Twitter) - and as is his right, has chosen to go for Wednesday rather than Tuesday just to keep everyone on their toes. The subject is around c**p code, something which we're all guilty of in some form of another either as a result of laziness or lack of knowledge.

My contribution is fairly short and sweet today and something I'm often guilty of: The hardcoded lookup value embedded within a Stored Procedure or Function to control logic. You'll have seen this before (certainly if you've ever worked with me!)

IF @TheIDOfThisPerson = 15
EXEC dbo.UseThisStoredProcedure
ELSE
EXEC dbo.UseThisOtherOne

I often do this as a "temporary" hack to test some code or demonstrate a proof of concept, but it shouldn't come as a surprise to me when this finds its way into production. Fortunately, its been rare when the issue has caught me out in any serious way but I often find myself fixing stuff in a test environment after promoting code through the Development tiers because the ID values don't match across all servers. There is almost always a better way to achieve this and its often only an extra few clicks on the keyboard but somehow it still creeps into my game and other developers I work with.

I don't think anyone consciously CHOOSES to write something poorly but as we're constantly learning, its an inevitable byproduct. But lets embrace it. Just as long as we don't beat ourselves (or each other) about it, we can use it in a positive way as a learning point. While a developers goal is often to write less code, I prefer to focus on writing less C**P code and I don't think you have to sacrifice one over the other.

Admin: can't drop impersonated login

This week, I came across an interesting behaviour while working with EXECUTE AS. I'd set up a test database to demo some work with a test login and when trying to tidy up the objects, I was faced with:

I can repro the behaviour with the following example. NB: there are 2 separate connections in this script.

/*
CONNECTION 1
*/

-- create a test database
CREATE DATABASE TestDropLogin;
GO
-- and a test login
CREATE LOGIN DropLogin
WITH PASSWORD = 'abc';
GO
USE TestDropLogin
GO
-- and finally a test user for that login
CREATE USER [DropLogin]
FOR LOGIN [DropLogin]
GO

EXECUTE AS LOGIN = 'DropLogin'
GO
SELECT ORIGINAL_LOGIN() -- 'DOMAIN\MyUser'
SELECT SUSER_SNAME() -- 'DropLogin'
GO
USE [master]
GO

/*
CONNECTION 2
*/
-- drop the database as we no logner need it
DROP DATABASE TestDropLogin;
GO
-- try and drop the login too
DROP LOGIN [DropLogin]
GO

Msg 15434, Level 16, State 1, Line 1
Could not drop login 'DropLogin' as the user is currently logged in.

That's because I didn't revert my execution context earlier and so I'm still logged in as DropLogin. If I go back to the 1st connection I can run the REVERT command.

Msg 15199, Level 16, State 1, Line 1
The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

But i've dropped the database where EXECUTE AS was called so issuing a USE command gives us:

Msg 911, Level 16, State 1, Line 1
Database 'TestDropLogin' does not exist. Make sure that the name is entered correctly.

So I don't appear to be able to REVERT the execution context and therefore can't drop the login. The only solution I found to this was to force the connection to close by issuing a KILL command.