GoSQLServer
Stuff for the Greater Omaha SQL Server User Groups
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.
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.
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.
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!
Doh! Sorry to be late getting this on the wire but the Heartland Developers' Conference for 2006 is has been announced. This year its in Omaha and runs from 26 October and 27 October. Hopefully I won't find myself in China again...
In its 3rd year, HDC06 is the largest independent annual professional Microsoft development conference in the nation, organized by user groups, and focused on .NET development and other emerging Microsoft technologies. HDC provides regional developers the opportunity to experience sessions usually reserved for Tech-Ed and PDC at a much lower cost while retaining the same nationally known presenters. Over 2 days, 23 sessions, 2 tracks, and several networking opportunities, HDC06 is where you’re find top knowledge experts that can prepare you for next level development.
For only $125 Early Bird/$175 standard, each Attendee Pass includes:
1 Conference pass to 2 keynotes and all 23 ninety minute sessions
- Meals including breakfast, lunch, snacks, and coffee breaks
- Access to the “Developer Lounge” expo and activity center
- Wireless internet access throughout conference area
- Pre conference networking party including drinks and food
- Thursday night “Developer Jam” including food, drink, and a casino for prizes!
- Attendee Kit and a chance to win a monster 64 Bit Alienware computer
The speaker list is looking awesome too: Microsoft's own Jeff Brand, Don Bryner and Jacob Cynamon. MVPs Dave Donaldson, Robert Hurlbut, Rocky Lhotka, Javier Lozano and Andrew Troelsen and community rockstars Craig Utley, Phil Wolfe, Robert Boedigheimer, Tim Gifford, Matt Milner and Nick Parker. I'm planning of giving a couple of talks on SQL Server 2005 for Developers.
Register here. And remember, we're talking a mere $125 one of the best Microsoft-focused technical conferences in the Midwest.
As Greg and Wally have reported, I've been thinking about bringing back Take Outs as a PodCast. I'm pleased to announce with the the hosting support offered by Steve Wynkoop of SSWUG, its now a reality. The need-to-knows:
The RSS feed: http://feeds.feedburner.com/sswugorgradio
iTunes friendly feed: http://feeds.feedburner.com/SswugorgRadio?format=pcast
Episode one show notes and download file.
Let's roll!
There's a bunch of .NET and SQL Server activity coming up in the Omaha area in the next weeks. Sad to say that I'm probably not going to be able to make most of them as I'll be on the road for DevelopMentor.
The Omaha.NET Users Group will be meeting on Thursday, January 26 at 6:00 PM. The meeting will be at DTN (90th and Dodge) where Jamie Wallingford of Sogeti will be presenting on ClickOnce. Then on Thursday, February 13 at 6:00PM Jeff Bramwell will be presenting on Team Foundation Server at Farm Credit Services of America, 120 & Q streets. More details on the FunWith.NET web site.
The Microsoft BigShow series will be rolling through town again on January 31st. The afternoon MSDN topics are Mobile Applications with .NET 2.0, Office-based solutions with VSTO and an introduction to WPF (AKA Avalon). You can register for that here. The IT Pro show runs in the morning with an introduction to Windows Server 2003 R2 at look at Microsoft's resource portfolio for the IT Pro. Register for this event here.
I'll be on the road doing classes in California, Flordia and Connecticut from the last week of January to the middle of February, so I'll have to miss most of these local events. Bummer. But I should make it to the FunWith meeting in February.
You might be wondering where any news about the Greater Omaha SQL Server Users Group has been or is. I wish I had better news, but both Luke and I have been swamped with work and unable to organize meetings on a regular basis. That doesn't mean we're not trying or that we've given up. I do expect we'll get the group going again once we've had a chance to do it right. Watch the GoSQLServer web site and RSS feeds for details.
The other day there was a posting in the SqlServer.XML newsgroup asking about Serialization. While the topic can get hairy quickly, there's a fairly simple solution if you're willing to live with a few... uh... interesting side affects. There's a fair amount of code in the example, so I've posted into an article over here.
Thanks to fellow DevelopMentor "Brain" Kevin Jones (RSS) for pointing this one out.
These problems left me stranded for days, then today, on another machine, I hit a different set of problems I kept getting the following "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." and if I set "User Instance" to false the following "An attempt to attach an auto-named database for file C:\[app_path]\ASPNetDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
..
If you don't want to follow the link, the short answer is that SQLExpress creates a directory per user in "c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" that it uses to store information. Deleting this directory has fixed both of my problems.
So while I'm relatively enjoying my time in Shanghai, I'm missing HDC -- the Heartland Developers Conference -- since I'm missing the chance to reconnect with friends and associates like Dave Donaldson, Robert Hurlbut, Rocky Lhotka, Javier Lozano, Matt Milner, Phil "McCracken" Rieck and of course and Joe and Phil. I've been following the blogs where I can of, course. But if you're at the HDC, please blog about and let me know your RSS address.
I'd like to give an shout of thanks to Robert Hurlbut for covering my talk for me. Robert rocks!
In the mean time, I'm watching Google Blog Search and Feedster for posts too.
A few days ago, I mentioned that Dr. Michael Kay got me really excited with his "Learn XQuery 10 Minutes" piece over on the StylusStudio web site. He got me exicited enough that I put together a SQL Server 2005 flavored version of it! Because its a fairly long piece, and I'm feeling lazy about cleaning up Word's messy HTML output, I've published it here as a PDF. It has a supporting script as well that you can download from here.
Note if you're having a hard time making the images out, you can see them online at http://www.sqljunkies.com/WebLog/photos/ktegels/picture16876.aspx
Comments on this welcome this blog post. Please do and keep in mind that its fairly rough and unpolished. Wrote this while attending today's MSDN Event here in Omaha. Not that it wasn't interesting, but I wanted to get this off my plate before PASS.
As you might expect, yesterday's announcement of the .NET Integrated Query Framework (LINQ) certainly has my attention. Its pretty hard to put down more than a stream of thought about this right now, but here's some of the implications as I see them.
- Its cool because, as a developer, I'll be able to express my queries in-line as part of the program
- That could be less than cool if I'm blindly dragging a lot data from the server to my application that I don't need and don't use but don't know that I'm doing that because my code is so "mindlessly simply."
- LINQ does mean you can spend less time learning the depths and eccentricities of T-SQL, XQuery and XPath
- LINQ doesn't mean you shouldn't you shouldn't spend time learning and really understanding T-SQL, XQuery and XPath. Why? See the next two points
- LINQ certainly does simplify writing code, but it doesn't help you design the application any better. There's nothing in LINQ that helps you decide, for example, if a dynamic SQL query is more appropriate than using a stored procedure. For that, you've got understand the SQL Server internals. As these things are expressed today in terms of the native language of the platform which is T-SQL. Same for XQuery in the sense that if you don't understand namespaces, XPath and schema -- you're going to struggle to getting optimal performance and usability of out of LINQ -- or anything else for that matter.
- LINQ is a Microsoft-specific generic query technology where as XQuery is a vendor and platform neutral query language over XML. What's the difference? If you're Developer working in a Microsoft-only world, you'll probably come to prefer XLINQ over XQuery for reasonably local data query for many reasons. If you're a developer who needs to work with many different technologies that can express their data and information in XML, XQuery is probably is probably going to be your choice since its an otherwise well-support standard.
- Microsoft Neither XQuery or T-SQL is depreciated or going away any time soon.
- However, LINQ is the first step towards an obvious end: A single query description and processing framework integrated at a very low level. Any query language, be it T-SQL, XQuery, XPath, XSLT, OPath, C#, VB.NET -- whatever -- can be compiled down to into a common query plan format and feed into a common query processing environment. The query processing environment could then sit on top of any data store, from Relational like SQL Server and JET, File stream and metadata like WinFS, Document storage or whatever.
So what the bottom line? LINQ is cool and will no doubt simplify the code writing effort. But that's not the big bang here. The big bang is that its the start of creating an entirely new way of distributing data, information and query over many network nodes. It gives us a way to write such queries our "native language" but have the executable over essentially anything anywhere.
That's truly powerful, but it shifts what you do have to know from "know the language" to "know the patterns." We've seen this before. Its what .NET for application development at a general level. With .NET, it doesn't matter much which language you use, but its does matter that you design the application right and that's where patterns apply.
Key Functional Enhancements
- SharePoint Web parts enable you to explore and view reports located on a report server by using Microsoft Windows SharePoint Services or SharePoint Portal Server.
- Reports can now be printed directly from within Internet Explorer. A Microsoft ActiveX control is provided to support a rich client-side printing experience including full page preview.
Go get it from http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en
Last week was fun for me as I got to do the things I like best. If I could have just avoided catching a cold, it would have even better.
Monday was my last day at HDR and we finished the work day off at Old Chicago. It was very hard to go and I really do wish those who stayed behind the best.
Tuesday I drove up to my Brother's place in Black River Falls Wisconsin. They still have standing snow and water in the fields up there. After a long day of driving through Iowa, nothing tasted better than a tall glass of Oscar's Oatmeal Stout. Yum!
One thing about driving through Iowa -- If you can, get an XM radio. Sure, you could play CDs until your ears bleed,