Database Mail Tips

In a tip on How to setup SQL Server alerts and email operator notifications, you have seen how you can configure email alert notifications in your SQL Server instance. There are cases where we cannot use SQL Server alerts like in a SQL Server 2005 Express instance or a clustered SQL Server 2000 instance as the MAPI interface used by SQL Mail is not cluster-aware. What other alternatives do we have for sending email alert notifications?

Some of our SQL Servers contain tables with summarized data (summarized on a weekly or monthly or quarterly basis). Previously this data was emailed as a flat file attachment to the recipients. I was asked to convert this in such a way that the email itself contains the table data. This way the recipients would be able to look at the data in a tabular format, making it easier for readability. Hence we arrived at the question of how to send email from SQL Server with data in a tabular format.

Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail. This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work. With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain. So how do you setup Database Mail?

One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account. In some cases you may not have a mail server, but still want to setup alerts. In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc...

In SQL Server 2005, Microsoft introduced Database Mail the replacement for SQL Mail. When setting up Database Mail a few options need to be set including enabling Database Mail, setting up profiles and also making sure that Service Broker is enabled. Most of these options can be configured on the fly by just selecting "Configure Database Mail", but when running the install and selecting "Yes" to activate Service Broker for "msdb" the installation just hangs and the install never completes.

One of the most welcome SQL Server 2005 enhancements over its predecessors is Database Mail. Database Mail is built on the Simple Mail Transfer Protocol (SMTP) as compared to SQL Server 2000 Mail which used a buggy and temperamental MAPI client causing significant frustration. Just like with everything else, every enhancement comes with its own learning curve. If you're new to Database Mail in SQL 2005 it is quite easy to get lost somewhere between the Surface Area Configuration Tool (where Database Mail is enabled) and SQL Server Management Studio (where the settings for Database Mail configured). I'll show you the basics of getting Database Mail configured for basic use in Management Studio and then show you a much quicker method...

I was in the middle of upgrading many servers to SQL Server 2005 and SQL Server 2008. As I was setting up Database Mail and the SQL Server Agent Alert System I wanted to create a script that I could use to do this instead of having to do this manually through the GUI. In this tip I provide a script with a few parameters that need to be setup to configure and turn on both of these features.

One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account. In some cases you may not have a mail server, but still want to setup alerts. In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc...

One of the most welcome SQL Server 2005 enhancements over its predecessors is Database Mail. Database Mail is built on the Simple Mail Transfer Protocol (SMTP) as compared to SQL Server 2000 Mail which used a buggy and temperamental MAPI client causing significant frustration. Just like with everything else, every enhancement comes with its own learning curve. If you're new to Database Mail in SQL 2005 it is quite easy to get lost somewhere between the Surface Area Configuration Tool (where Database Mail is enabled) and SQL Server Management Studio (where the settings for Database Mail configured). I'll show you the basics of getting Database Mail configured for basic use in Management Studio and then show you a much quicker method...

Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail. This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work. With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain. So how do you setup Database Mail?

Some of our SQL Servers contain tables with summarized data (summarized on a weekly or monthly or quarterly basis). Previously this data was emailed as a flat file attachment to the recipients. I was asked to convert this in such a way that the email itself contains the table data. This way the recipients would be able to look at the data in a tabular format, making it easier for readability. Hence we arrived at the question of how to send email from SQL Server with data in a tabular format.

I was in the middle of upgrading many servers to SQL Server 2005 and SQL Server 2008. As I was setting up Database Mail and the SQL Server Agent Alert System I wanted to create a script that I could use to do this instead of having to do this manually through the GUI. In this tip I provide a script with a few parameters that need to be setup to configure and turn on both of these features.

In a tip on How to setup SQL Server alerts and email operator notifications, you have seen how you can configure email alert notifications in your SQL Server instance. There are cases where we cannot use SQL Server alerts like in a SQL Server 2005 Express instance or a clustered SQL Server 2000 instance as the MAPI interface used by SQL Mail is not cluster-aware. What other alternatives do we have for sending email alert notifications?

In SQL Server 2005, Microsoft introduced Database Mail the replacement for SQL Mail. When setting up Database Mail a few options need to be set including enabling Database Mail, setting up profiles and also making sure that Service Broker is enabled. Most of these options can be configured on the fly by just selecting "Configure Database Mail", but when running the install and selecting "Yes" to activate Service Broker for "msdb" the installation just hangs and the install never completes.

One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account. In some cases you may not have a mail server, but still want to setup alerts. In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc...

Some of our SQL Servers contain tables with summarized data (summarized on a weekly or monthly or quarterly basis). Previously this data was emailed as a flat file attachment to the recipients. I was asked to convert this in such a way that the email itself contains the table data. This way the recipients would be able to look at the data in a tabular format, making it easier for readability. Hence we arrived at the question of how to send email from SQL Server with data in a tabular format.

I was in the middle of upgrading many servers to SQL Server 2005 and SQL Server 2008. As I was setting up Database Mail and the SQL Server Agent Alert System I wanted to create a script that I could use to do this instead of having to do this manually through the GUI. In this tip I provide a script with a few parameters that need to be setup to configure and turn on both of these features.

In a tip on How to setup SQL Server alerts and email operator notifications, you have seen how you can configure email alert notifications in your SQL Server instance. There are cases where we cannot use SQL Server alerts like in a SQL Server 2005 Express instance or a clustered SQL Server 2000 instance as the MAPI interface used by SQL Mail is not cluster-aware. What other alternatives do we have for sending email alert notifications?

One of the most welcome SQL Server 2005 enhancements over its predecessors is Database Mail. Database Mail is built on the Simple Mail Transfer Protocol (SMTP) as compared to SQL Server 2000 Mail which used a buggy and temperamental MAPI client causing significant frustration. Just like with everything else, every enhancement comes with its own learning curve. If you're new to Database Mail in SQL 2005 it is quite easy to get lost somewhere between the Surface Area Configuration Tool (where Database Mail is enabled) and SQL Server Management Studio (where the settings for Database Mail configured). I'll show you the basics of getting Database Mail configured for basic use in Management Studio and then show you a much quicker method...

In SQL Server 2005, Microsoft introduced Database Mail the replacement for SQL Mail. When setting up Database Mail a few options need to be set including enabling Database Mail, setting up profiles and also making sure that Service Broker is enabled. Most of these options can be configured on the fly by just selecting "Configure Database Mail", but when running the install and selecting "Yes" to activate Service Broker for "msdb" the installation just hangs and the install never completes.

Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail. This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work. With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain. So how do you setup Database Mail?