Enjoy Every Sandwich

Moved. Again. Sorry about the confusion.

Okay, the dust has finally settled and I've moved into my new Blog home.

See you there, not here.

Welcoming 2007.

To be frank, I'm glad that 2006 is over. Technically it was a great year with the coming of age of SQL Server 2005 and .NET 2.0. But personally, it was a pretty hard year that end on an ugly note. But I'm back on my feet and feeling good that 2007 will be better times.

I'm working on a couple of longer technical blog posts on SQL Server 2005 -- look for them once this week settles down.

DENIED! (subtitled: using LOGON TRIGGERS with SQL Server 2005 SP2)

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.

And now for something completely different: a week of ASP.NET 2.0

Turns out that I'm spending an extra week in Los Angeles, but this time its for something new for me: I'm helping Brock Allen and Jason Diamond with next run of Guerrilla ASP.NET 2.0 at the Hilton South Bay.

In some ways, this is coming full circle for me. Many years ago, I took DevelopMentor's ASP.NET 1.0 class (when the product was still in Beta) and that's what got me excited about what DM does. Its going to be fun to getting back to ASP.NET after a year or so away from it on a day-to-day basis.

Dude, are you ready for Primetime?

Just in case you've not already read it on Gert's or Cameron's blogs, CTP6 for "Visual Studio Team Edition For The Database Professional" -- AKA DataDude -- just got released. If things hold true to form, this will hopefully be the last CTP before we start seeing RCs. Looks to me like they've addressed a number of the things that kept me from using "Dude" as my primary SQL Development environment. Gert listed the improvements as:

Full support for SQL Server 2000 & 2005 objects, the parser work has been completed (so I can't wait to test it again with SQLCLR Assemblies and Service Broker code).
Extended Properties support, we know import and deploy all your extended properties (so my database dictionary programs will resume working again).
Inline constraint support, if you do not want to separate them out, we allow them inline as well (cool for check constraints)
Pre- and post deployment scripts population during Import Script
Full support for command line build & deploy and Team Build
A new Import Schema Wizard which is also integrated with the New Project Wizard to make project creation and import a single stop shop (Thank goodness!)
Synchronize your database project from Schema Compare, compare your project with a database and pull the differences in to the database project
Schema refactoring is now allowed even if you have files in a warning state
Resolve 3 and 4 part name usage when the referenced database is locally present, same for linked servers
The product no longer installs SQL Express; you can pick your own SQL Server 2005 Developer Edition or SQL Server 2005 Enterprise Edition instance on the local box. When you first start the product for the first time we will ask you to choose an local instance to use (YEAH!)
Display detailed Schema Object properties in the VS Property Window for selected objects in the Schema View (Can't wait to kick the tires on this)
Separation of user target database settings through user project files, this allows users to work against a different target instance without changing the main project file.
We made great progress on the overall stability and performance of the product across the board, project creation, importing your schema, reloading project and making changes to your schema
And last but not least we fixed many reported customer problems!

You can read the full text of Gert's announcement here or get this bits directly from here.

Pour mon etudiants des BTUG de Montreal

Au commencement, laissé à moi la parole vous remercient d'être pour de tels bons étudiants et pour lui faire très une semaine de divertissement de moi! Les programmes de document et de démonstration sont maintenant disponibles sur l'adresse du FTP que j'ai annoncé vendredi.

Merci encore, et apprécier votre prochain cours avec Rod sur le «Windows Workflow»!

To start, let me repeat a big thank you for being such great students and making the week very entertaining for me. The demonstration files and so on are available in the URL we shared on Friday.

Thanks agai, and enjoy your class with Rod on "Windows Workflow!"

Assembly Vs. Assembly

I think of one the most mystifying error messages that you can get in SQL Server 2005 is when try to catalog the same assembly twice into the same database:

Msg 6285, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "...".

This raises a couple of interesting questions.

a.) What the heck is an MVID and why do we care?

b.) How can we check these MVIDs for ourselves?

a.) MVID stands for Module Version ID and is documented in section 21.27 of ECMA-335 as "simply a Guid used to distinguish between two versions of the same module." Although an Assembly can contain many modules, there is a principal MVID assigned to it in the manifest -- the Manifest MVID. One of the checks that SQL Server performs when cataloging an new assembly is to get it's Manifest MVID and compare that to all of the other Manifest MVIDs for assemblies already cataloged in the current database. When a duplicate is found, SQL Server 2005 rejects the inbound assembly.

b.) Checking any non-database assembly for its Manifest MVID is fairly simple. Here's a short snip of code that returns it:

Assembly asm = Assembly.Load(BUF);
Module m = asm.ManifestModule;
return m.ModuleVersionId;

Where BUF is assumed by an array of bytes holding the assembly you want the MVID for. You could also use Assembly.LoadFromFile to an exsiting Assembly-as-DLL file into the snip, however, that method is not allowed within SQLCLR. An easier way to get the MVID for an Assembly already cataloged into SQL Server 2005 is to use the AssemblyProperty statement in T-SQL, e.g.:

select assemblyproperty(assembly name,'mvid')

ADO vNET, the bits are starting to rollout

A few weeks back, the ADO.NET team released the first in a series of ADO.NET v.Next releases. This was our first look at several exciting technologies like the Entity Data Framework and Mapping Views. Well today, the team announced the release a designer for working with ADO.NET v.Next Entity data.

Go have a look-see.

Monteal? Son gentil!

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!

Must have RSS subscription: The SQL Query Processing Team is blogging

Time to fire up your favorite RSS feed reader and get to subscribing:

http://blogs.msdn.com/sqlqueryprocessing/rss.xml

No SQL Developer should leave home without it.

Loading an XML Schema Collection from a File

A commonly asked question is "how can I load an existing XSD file into an XML Schema Collection?" It's pretty easy:

declare @don xml
select @don=bulkcolumn from openrowset(bulk 'c:\users\ktegels\some.xsd',single_blob) as s
create xml schema collection dbo.collectionName as @don
go

Subsituting the location for your file following the BULK provider in the OpenRowSet source.

Give Microsoft a piece of your (Datawarehouse) mind!

Are you Datamart Builder with few bones to pick? Are your cubes too slow?

Ok, so I can't do anything to help you with that. But you can help Microsoft help you. You see, they are in the planning phase for the next version of SQL Server. Your input counts! Visit the SQLTIPs blog for a link to the online surveys.

I/O, I/O, its off to the disk we go...

One of the three best PSS SQL Server Engineers -- Bob Ward -- has written up a great set of white papers on SQL Server I/O. More about them on the SQL Blog....(read more)

What's the third Thursday of May in 2017?

The 18th. So, okay, that's not a very interesting question in and of itself. A better question might be "what's the Nth occurrence of the Mth day in each month?" Like, what are all of the third Thursdays in 2017?

The literal -- and rather boring -- answer is January 19, February 16, March 16, April 20, May 18, June 15, July 20, August 17, September 21, October 19, November 16, December 21.

So what's the interesting question here? How can one compute these using a T-SQL query. If you are using SQL Server 2005, that's not too hard as this Table Valued Function demonstrates:

create function dbo.NthGivenWeekDaysForYear(@year smallint,@gap tinyint,@dayOfWeek tinyint) returns @d table(date datetime) as begin -- fill up a table variable for the days in the requested year declare @days table(date datetime,dayOfWeek tinyint,Month tinyint); declare @ds datetime, @de datetime; set @ds = cast(cast(@year as varchar(4)) + '-01-01' as datetime) set @de = dateadd(yyyy,1,@ds); while(@ds LESS-THAN @de) begin insert into @days values (@ds,datepart(dw,@ds),datepart(MM,@ds)); set @ds = dateadd(dd,1,@ds); end; -- This CTE computes the occurrence number for each weekday within the given -- month (e.g., first Monday gets a seq of 1, second Monday is 2...) Resets -- with the month changes. Filters down to just the desired date. with c as (select date,row_number() over (partition by month order by date) as seq from @days where dayOfWeek=@dayOfWeek) -- We can now insert the target dates. We know that seq has the -- nth occurrence of that weekday in the month. insert into @d select date from c where seq = @gap; return; end;

The magic here is in the row_number() function and in its over clause. Here the row_number() is computed as a sequentially increasing value for each occurrence of a given weekday within a month. We sort by date here so the first day of interest gets the value of one in the sequence. The partition by reset the sequence to one when the month changes.

So why would we want to write this as a TVF rather than say a stored procedure? The immediate answer might not be so obvious, but here's a use case: suppose your company has started a bonus program for newly hired employees. Employees are eligible to participate in the program so long as they were hired before the third Friday in the month when payroll data get generated. All it takes to refractor this function is a couple of tweaks. First, we need to have the function accept a hiredate as a parameter. Second, we need to modify the final insert statement to this:

insert into @d select date from c where seq = @gap and datepart(m,date) = datepart(m,@hireDate) and datepart(d,@hireDate) LESS-THAN-OR-EQUAL-TO datepart(d,date)

We can now leverage the TVF using the new APPLY operator, in this case, the OUTER APPLY variant thusly:

select EmployeeID,hireDate,date from adventureworks.HumanResources.Employee e outer apply dbo.NthGivenWeekDaysForYear(2002,3,6,e.hiredate) where datepart(yyyy,e.hiredate) = 2002

Because Employee 284 missed the cutoff by one day, the TVF returns a null value for that row, and we know that this employee didn't qualify for any bonus for May of 2002.

One of the best evaluation comments ever.

We're just wrapping a teach of Guerrilla SQL Server and I was reading the evals. While I was happy with all of the comments, but one really deserves calling out:

"niels f*cking rocks! best instructor i've ever had period."

I agree! Way to go Niels!

And just so you know, it rocks even more to work with him.