My Inner Geek

Intellisense in SQL Server -- can't wait

2007-11-07T02:17:00.001-05:00

This was sent to me from another member of the Toronto SQL Server user group. For those following the CTP releases of SQL Server 2008, a feedback post as been placed requesting a Goto Definition functionality in the SQL Management Studio.

Goto is a cool idea (hat's off to DaringDave wherever you are) but not likely to make it into the release code. However, I found something more interesting in the response. In the comments on this suggest, Microsoft adds:

"Transact SQL Intellisense is a part of the plan in SQL Server 2008. On the road of planning and execution, we will consider GoTo Definition as a feagure [[sic] candidate and address it in a future release of SQL Server."

Intellisense has been promised since SQL 2005 and has yet to show up in a CTP. This is the most concrete confirmation I have come across so far that we will actually see this feature before SQL 2008 goes to Gold code.

As someone who spends a lot of time programming in T-SQL, I can't wait to see this feature. It's always the small time saving features that are the coolest

My fingers are crossed in anticipation of the next CTP release (which I hope we will see before Christmas).

I'm Back (sort of)

2007-11-06T01:23:00.000-05:00

I started this blog as an experiment. I wanted to learn the tools and get a feel for what blogging was all about without much sense of what I wanted to say. Ok, not an auspicious start, but we all have to begin somewhere and a least I wasn't sharing angst ridden poetry or personal details that no one would care about.

Then, in a personal branding exercise, mostly driven by my partner in crime Gina Lijoi I switched over to https://stevegiles.blogspot.com. However, I was having trouble finding a suitable tone writing both business oriented and purely technical posts in the same blog.

Now that I'm starting to focus on content more and more, I am going to return to this blog, even though I'm not crazy about the URL from an SEO perspective, as the home for my technical articles and will keep the other site for my business oriented posts.

Stay tuned.

--Steve

Toronto Rocked the Launch

2005-11-10T23:08:00.000-05:00

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

2005-11-07T23:45:00.000-05:00

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

2005-10-12T22:10:00.000-05:00

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

2005-09-30T00:16:00.000-05:00

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