About me

January 2008 Blog Posts

One the more welcomed enhancements of SQL Server 2005 Notification Services was the new Microsoft.SqlServer.Management.Nmo namespace. The namespace provides classes that may be used develop and administer SSNS instances and applications.
For example, the following C# console application may be used to iterate through each SSNS instance of a SQL Server 2005 instance, printing its name to the console window. If you'd like to try this code for yourself, don't forget to add a reference to the microsoft.sqlserver.smo.dll in your Visual Studio 2005 project.
using System;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Smo;
namespace ConsoleApp
{
class Program
{
...

As many of you may know, for the past six years I've volunteered my time and energy to an organization called PASS - the Professional Association for SQL Server. It's a wonderful organization of highly dedicated, talented, and personable SQL Server professionals from around the world.
My time on the PASS board began in January of 2002 when I was appointed to fill a vacancy on the board created when a director resigned mid-term. I really enjoyed working with such a dedicated and enthusiastic group of people and I sought re-election at the end of my first year.
Now, three elections and...

In a prior posting, I demonstrated how the undocumented sp_MSforeachtable stored procedure can be used to iterate through each of the tables within a database.
In a separate posting earlier this year, I also demonstrated how the DBCC CLEANTABLE command can be employed to reclaim space that was once consumed by variable length character columns that have been subsequently dropped.
In this post, I'd like to combine the two topics and show how the sp_MSforeachtable stored procedure command may be used in conjunction with the DBCC CLEANTABLE command to reclaim space from all tables after one or more columns were...

In my initial post here on the SQLTeam site, I mentioned that for the prior three years I had blogged on another site. That blog was almost exclusively dedicated to SQL Server Notification Services.
If you are a regular reader of this new blog, you've undoubtedly noticed that I've broadened the scope to include topics of interest to DBAs, database developers, and general technologists.
As time permits, I'm still attempting to recover the SSNS tutorials and commentary from my prior blog. I'll post what I can recover as it becomes available.
In the meantime, here are a few links to...

In a recent post, I demonstrated how SQL Server will reuse space made available when rows are deleted from a table. This occurs automatically without any outside intervention on our part.
However under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. We can employee a DBCC utility reclaim the newly-freed space.
To illustrate this behavior...

If you've ever attended a DevTeach/SQLTeach event, you already know that it's a wonderful and intimate community-driven event designed to provide top-notch .NET and SQL Server developer education in an up close and personal environment. If you haven't attended and you use Microsoft developer tools, I highly recommend the event. The next event is in Toronto, 12 - 16 May, 2008. Make your plans now.
And, if you're interested in sharing your knowledge by speaking at the event, the call for abstracts is open for one more week. Here's a link with more information.
I hope to see you there!
Joe

A question that occasionally comes up from people who have made the switch to Microsoft SQL Server from Microsoft Access is "If I delete a bunch of rows, do I need to compact my SQL Server database?"
It's been many years since I've used Access, but I still remember the reason for their concern. Microsoft Access would continually add rows to the end of the table. If some, or even all, of the rows were deleted from the table, Microsoft Access wouldn't reuse the space. It just kept adding rows to what was the very end of the table and...

Even though SQL Server 2008 does not include the Notification Services (SSNS) component, it seems that companies still consider SSNS a very viable option for upcoming notification projects. I'm planning to write a blog or SQLTeam article in the near future to address some questions I'm frequently asked.
In the meantime, if you have decided to create a new notification application using SSNS, I'm reposting a short tutorial I created a couple of years ago. It's been modified and updated slightly.
In this blog we'll go through the simple steps that can be used to create a new SQL...