Database Mail, Part 1 - Set up

Over the years I have come across many reasons for Database Mail to be activated, and a couple of reasons not to use it. The main reasons for activating it are the added flexibility that can be achieved in reporting the success or failure of maintenance jobs or any unattended processes and for monitoring and notification purposes. As a business tool for sending e-mail, it is sufficient for small numbers of e-mails but I would always recommend using an application that produces a well-formed e-mail with all of the appropriate attributes rather than using Database Mail.

Read more

Database Mail, Part 2 - Creating an Account

In Database Mail, Part 1 - Set up I produced a stored procedure that enables Database Mail. The second stage is to create an account which determines the e-mail address that messages will be sent from and the SMTP server that will be used to send the e-mails. It is worth noting that if your organisation has primary and secondary SMTP servers then accounts can be set up for both of the servers, or as many as your organisation has.

Read more

Database Mail, Part 3 - Creating a Profile

In Part 1 of this series I produced a stored procedure to enable Database Mail and in Part 2 I produced a stored procedure to create an account. Now, to be able to use an account, and to send an e-mail you need to have a profile. A profile is related to one or more accounts and is the means by which you identify how the e-mail will be sent. The procedure that I have written will create a profile if one does not exist and will associate an account with it based on the @aRelationshipSequence.

Read more

My stored procedure best practices

A quick search on the internet for examples of SQL will return a multitude of examples, all of which seem to adhere to different conventions for laying out and formatting SQL. I have always advocated coding standards to allow all procedures to follow similar naming, layout and formatting conventions. The main benefit that I have found is that it is easy to understand the procedures. I was going to document all of my conventions but I found a blog entry by Aaron Bertrand that covers a lot of what I would have said.

Read more

Space on Server Disk Drives

A common requirement when managing the resources of a database server is to be able to monitor the space available of the disk drives. Although there is no obvious way to do this through T-SQL there is an undocumented extended procedure called xp_fixeddrives. As always with the undocumented procedures, it is not recommended to use this procedure in a production environment as Microsoft reserve the right to change or remove this procedure as pat of hot fixes, cumulative updates or service packs.

Read more

When was a database backed up?

A common requirement is to be able to report on the backup status of each database in an instance. There are of course many tools out there that do just this, but what if your organisation has not purchased one of these tools, or the tools are not installed, for instance in a development environment? Here I present a stored procedure that will produce a table summarising the status of each database in an instance based on the type of backup that you are interested in.

Read more

How to change the default location of SQL Server Backups

One task that I have found very useful, especially on development servers that have a multitude of databases that are created and backed up in an ad-hoc manner, is to change the default location of the backups. This means that all servers can store ad-hoc backups in a location that conforms to corporate guidelines. It is not possible in SQL Management Studio to make this change. It is possible to make tis change directly in the registry but it is not repeatable.

Read more

Laptop Rebuild - Step One

I am at the point once again when my laptop has accumulated a load of stuff that I do not need, nor do I want. This includes remnants of Beta versions of software that is now in production and software for phones and mobile devices that I no longer use and cannot uninstall. The result is the regular re-build from the OS up. The choice of OS is easy, Windows 7 Ultimate.

Read more

Laptop Rebuild - Step Two

Yesterday I installed the OS, AV and office so that I have access to e-mail. Next on the list are the additional Microsoft tools that I use in my job: Project Professional 2010, Visio 2010, Power Pivot and the various Outlook connectors. During the installation of these other products, I am making some tweaks to Office. I have found that the best way to do this is to open Word and change the options in

Read more

The importance of Active Directory

One of the roles in my last position was to set-up and manage Project Server 2010. As I had never used Project Server (2010 or any other version) this was quite an undertaking. One of the first things I looked at was the configuration of the users and the resources. In Project Server parlance a user is someone who can access the system and a resource is a person, object or material that can be allocated to a task within a project.

Read more