Rants
Sometimes you've just got to.
Two of the cool new features that we talk about in Essential SQL Server 2005 and Guerrilla SQL Server 2005 are:
- Data Definition Language (DDL) Triggers, which allow us to
catalog code that gets fired synchronously with event that trigger it. This is
useful in cases where you want to hook into the processing sequence for
commands like CREATE TABLE, ALTER VIEW or DROP ASSEMBLY, potentially logging
who did what or issuing a ROLLBACK to cancel that work.
- EVENT NOTIFICATIONS, which route information about events
asynchronously to Service Broker queue after the event has been processed.
EVENT NOTIFICATIONS are available for 193 specific events, including the events
we can use for DDL triggers.
This processing behavior difference is critical. EVENT NOTIFICATIONS
cannot easily be used to cancel work before it gets done, which a DDL trigger
can do. On the other hand, DDL triggers can’t be written for all 193 possible
events. So there are times we find ourselves wanting to use a synchronous
trigger for non-DDL events. For example, consider the routine situation where --
at the end of the month -- a given SQL Server needs to be dedicated to running
long-running and demanding batches and procedures to close out the financial
records. Normally, a "black-out" period is set by management for some interval (like
8:00 PM on the last day of the month) until work resumes the next day. The
question becomes: how can we easily automate the enforcement of this policy?
Well, if we could inject code into the logon process, it
would be easy. However, SQL Server hasn’t really made it easy to do that. At
least before Service Pack Two for SQL Server 2005. In this service pack a new
type of trigger is introduced: the LOGON TRIGGER. Let’s see how they work by
creating one.
What I’d like to do for auditing and troubleshooting
purposes is first create a database with just one table in it that allows me to
record logon activity. There’s nothing new about coding that up:
use master
go
if not db_id('audit') is null
drop database audit
go
create database audit
go
use audit
go
create schema admin
go
create table admin.LogonAudit(eventData xml,denied bit default 0)
go
use master
go
Let’s also create a user to test with:
if not suser_sid('userLogin') is null
drop login userLogin
go
create login userLogin with password = 'someEasyPassword'
go
Our trigger is actually fairly simple to write:
-- Drop our trigger just in case an older version exists...
if exists(select * from master.sys.server_triggers where name like 'noUserLogins%')
drop trigger noUserLoginsDuringMonthEnd on all server
go
-- Name it
create trigger noUserLoginsDuringMonthEnd
-- Applied at the instance level
on all server
-- Execute this code as the person who cataloged it
with execute as self
-- New, fires when a logon is attempted
for logon
as begin
-- Get the range of the backout period
-- starts on the last day of the month at 8PM
-- and ends at 9 hours later
declare @bob datetime,@boe datetime
select @bob = dateadd(m,1,getdate())
select @bob = convert( datetime,
cast(datepart(yyyy,@bob) as char(4))+'-'+
cast(datepart(MM,@bob) as char(2))+'-'+
'01 00:00:00'+' ', 120)
set @bob = dateadd(hh,-4,@bob)
set @boe = dateadd(hh,9,@bob)
-- If its currently in the back period and
-- a disallowed login is tried...
if (getdate() between @bob and @boe) and
(original_login() = 'userLogin')
begin
-- Stop the login
rollback
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),1)
-- See what happens to messages...
raisError('Attempted user login during month-end blackout',16,1) with log
end else
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),0)
end
go
So what happens if somebody tries to logon using the
userLogin we created during the black-out period? They get a message like this:
Personally, I have mixed feelings about this. It is great
because it works and does a nice job NOT displaying my potentially
security-sensitive error message (from RaisError) to the user. However, the
error message shown gives away two pieces of information it doesn’t need to:
the fact that a trigger nixed the log-on ("due to trigger execution") and that
there might very well be some database on the system called "master." I’m not
really sure why the SQL Server team decided to have this dialogue show those,
so I filled a bug about it. If you agree that this isn’t good, you can show
your support by voting for my bug by voting in it at https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008
That said there’s no doubt that this is a very nice addition
to SQL Server 2005 and your bag of tricks.
I'm teaching a class in Montréal this week and, honestly, this is quickly becoming one of my favorite cities. Many thing have stood out for me, but the most noticable thing for me is just how quiet this city is. The roar of the traffic, the scream of sirens and the general din of having thousands of people concurrently on the streets just isn't here. And, while I don't speak much French at all, I've had almost no problem getting along and getting business done.
Of couse, there are some other notable differences. The no ice skating signs. Nearly filling a stadium for a PRE-session hockey game. Sushi Bars on every other block. Decent Beer. Yeah, like any of those things are a problem for me.
Yes, its a little price and sure, its gets a bit colder at night than I'm used to. But I find myself saying. Montréal -- Son gentil!
Those of you who've met me probably have figured out that I'm not the most agile person ever. One time in Gym class, when they were trying to get me to play basketball, the coach said "you're the only kid I've ever known who can't pivot." I never thought this would come back to haunt me (its not like I try to play basketball), but it did recently when working with a matrix. Its not hard to imagine treating a table in SQL Server as a matrix. What is hard -- or at least is a lot of code to write -- is doing a join on matrix to some other table. Consider for example the case of an Italian resturant. They've decided to use SQL Server to manage a number of things and a couple of them are lists of dished they prepare and a table that organizes these dishes into four-course meals. The tables might be structured something like this:
create table dbo.menuItems (itemID tinyint identity(1,1) primary key,name varchar(30) not null unique,meatless bit not null)
and
create table dbo.menu(mealID tinyint identity(1,1) primary key,antipasti tinyint,primi tinyint,secondi tinyint,dolci tinyint)
After adding some data, the contents of dbo.menu look something like:
mealID antipasti primi secondi dolci
------ --------- ----- ------- -----
1 1 5 9 13
2 2 6 10 14
3 3 7 11 15
4 4 8 12 16
And that's nice, but its not very usable by a human being. What I'd really like to get is:
mealID antipasti primi secondi dolci
------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 Crostini Ravioli Nudi di Pesce Pastello di Pesce Granita
2 Prosciutto e meloni Risotto alla Milanese Ossobuco Timballo di Pere
3 Carpaccio Stracciatella Saltimbocca Castagnaccio
4 Polenta Fritta Trippa alla Fiorentina Bollito Misto Zabaione
But how do you do that? In SQL Server 2005, a couple of new operators make this pretty easy: UNPIVOT and PIVOT. But how and why? Let's consider our matrix could also be represented as a list of paired values:
course itemID
---------- ------
antipasti 1
primi 5
secondi 9
dolci 13
antipasti 2
primi 6
secondi 10
dolci 14
antipasti 3
primi 7
secondi 11
dolci 15
antipasti 4
primi 8
secondi 12
dolci 16
The unpivot operator makes that's easy to do:
select u.menuID,itemID from dbo.menu m unpivot (itemID for menuID in (antipasti,primi,secondi,dolci)) as u
And it makes joining the dishes in that list back to the table of dishes (dbo.MenuItems) rather easy: select u.menuID,i.name from dbo.menu m unpivot (itemID for menuID in (antipasti,primi,secondi,dolci)) as u join dbo.menuItems i on u.itemID = i.itemID So while that's cool, we're still dealing with a pair-list, not a matrix. Now, I might be a white guy that can't jump, but I certainly can PIVOT back into the matrix.
;with menus as (select mealID,u.menuID,i.name from dbo.menu m unpivot (itemID for menuID in (antipasti,primi,secondi,dolci)) as u join dbo.menuItems i on u.itemID = i.itemID) select * from menus pivot(max(name) for menuID in ([antipasti],[primi],[secondi],[dolci])) as p
Take that, Coach!
You’d think life would look easier once you are “over the hill.” Hardly a surprise, it doesn’t. So this morning as I awoke for my 14,610 day, I had to ask myself “what have a really learned so far” -- aside from a few hundred bad jokes and puns and inventory of geeky knowledge that is? Seems like anything else meaningful boils down to one of three things:
1. The actual whole always exceeds the apparent sum
2. The only easy day was yesterday
3. It is okay to be imperfect
There’s not much elaboration needed or, frankly, even possible on this.
But today is normally the day I make my “life altering” decisions for the year and it is time for me to go public with a few things. First, I owe a big debt of thanks to my life partner of the last ten years. Janell has been both a great influence and tremendous asset in my life. However, some relationships – no matter how loving or great – can’t weather the sorts of changes my life has taken in the last few years. Janell and I essentially parted our ways back in June of this year. She’s met somebody that’s ideal for her and is making plans to be with them permanently. I really couldn’t be happier for them. Sure, breaking up is hard and sad. Given my crazy travel schedule and that they have bonded well to her, we’ve decided that she gets to keep the Cats. While I’m sure I’ll miss Janell more than I will them, it was almost a non-decision to let go here. They’ll still get plenty of love and play in their new world to.
As for me, I wasn’t going to just sit on my butt and feel sorry for myself for long. Back in June I started usi