Toronto Rocked the Launch

Just a quick word for those who missed the first of Canada's SQL Server 2005 and Visual Studio 2005 Launches in Toronto it was awesome.

There were more people in TO (about 2800) than were present to see Steve Ballmer in San Francisco the day before.

Hat's off to Damir Bersinic and Barnaby Jeans for putting on a great show on the SQL Server side.

Fun with DDL Triggers

With all the big changes in SQL Server 2005 I've been focusing on some small but significant changes. The first of these is the Data Definition Language (DDL) triggers.

In a nutshell, SQL Server 2005 will allow you to set a trigger at the database level for a myriad of DDL actions (consult your friendly neighborhood BOL for the full list).

I've chosen an particular application of this fuctionality here, one that solves a problem I had with earlier SQL versions -- table management.

The problem is like this: suppose you, as DBA, notice that a table has been deleted. You go into the SQL Server logs but find no reference to the dropped table. Nor can you find out who dropped it and when.

Now you can:

In a test database, I created a log table in my test DB that looked like this:

CREATE TABLE [dbo].[DDL_Log]
( [id] [int] IDENTITY(1,1) NOT NULL,
[DDL_Event] [nvarchar](100) NOT NULL,
[TSQL] [nvarchar](2000) NOT NULL,
[ExecutedBy] [nvarchar](100) NOT NULL DEFAULT current_user,
[Action_date] [datetime] NULL DEFAULT (getdate()))
ON [PRIMARY];


I then created a DDL trigger on the database using the following syntax:

CREATE TRIGGER [log_table_actions]
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS BEGIN
DECLARE @data XML
SET @data = EVENTDATA()

INSERT dbo.ddl_log (DDL_Event,
TSQL,
ExecutedBy,
Action_Date)
VALUES
(@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar (2000)'),
CONVERT(nvarchar(100), (CURRENT_USER)),
GETDATE()
)
End;

A few thing will jump out immediately. The first is that the trigger is created "ON DATABASE" rather than on a table and secondly, that the trigger is create on all CREATE, ALTER and DROP TABLE statements.

Finally it uses the eventdata() function to query information on the DDL statement that called the trigger.

Please note that this function returns XML, so you'll need to capture the output inside and XML typed variable, and you need to query elements inside the element to get the data. In this case, I've returned the event type and the full TSQL code for the DDL statement.

I'm still looking for the full schema for the eventdata() xml document. But that will be a post for another day.

Some good SQL 2005 resources from Microsoft

Hi All,

For those who are interested in SQL Server (and not living under a rock) I'm sure you are aware that the SQL Server 2005 launch is coming up this November. If you are in Canada, registration has already begun with the first session in Toronto on November 8.

If you haven't registered yet, I've heard from sources at Microsoft that the seats are filling so you should reserve soon at the Microsoft Events site (click here).

To get ready I've been getting all the resources I could get my hands on (for free of course).

I've found two great resources that are worth checking out. The first is that Microsoft is offering free 90 day access to a number of SQL 2005 e-learning courses.

Also, they have gathered links all past and future webcasts and posted links on the SQL 2005 home page. They have broken the web casts out by MSDN and TechNet topics. Also, to make life for some easier, they have also created a BI webcast page that pull BI related content from both TechNet and MSDN. This may not seem earth shattering news, but I've always found it a bit hard to find webcasts so having them in one place is actually not a bad thing.

You can find the links to both e-learning and the webcasts (as well as lots more resources) at (http://www.microsoft.com/SQL/2005/default.mspx).

Now I just need a way to do away with the annoying need to sleep and I might be able to get through everything.

Welcome

Greetings

The world of IT is getting so fragmented that its almost impossible to keep up. I've found blogs to the the best way to at least hear about what's really going on out there.

So I decided to add my voice to the chorus. Hopefully you will find some interesting facts here and spread the word. I'm also looking for a soap box of my own so expect some attitude as well.

Cheers,

Steve