Siusic Dot Com

For a long time the only way to send email in your SQL query is to use the system store procedure xp_sendmail. To use this system store procedure you have to enable SQL Mail which relies on MAPI protocol and you will have to create a mail profile on the server. That was prior to SQL 2005. In fact I never see SQL Mail works in an environment that doesn’t use MS Exchange Server. Maybe SQL Mail was a design on purpose so that Microsoft can cross sells its products.

However there is way to get around SQL Mail and use SMTP server to send mail in SQL query.

In SQL 2000 or SQL Server 7.0 if you want to send mail using SMTP in your query, you will have to rely on thirty party COM components and using store procedure sp_OACreate to create an instance of the OLE object on SQL Server. After that you can use sp_OAMethod store procedure to call the method that the OLE object exposes to send email. I have used Persits’ AspEmail component for a longtime.

In order to use AspEmail in your SQL query you will need to install and register the component on the server that runs SQL server. If your SQL Server is clustered you will need to install and register it on all nodes. Otherwise your send mail functionality implemented using AspEmail will fail if your SQL server fails over to a node that doesn’t have the component installed. You will need to have a SMTP server ready. After that you can write your own send mail store procedure using the AspEmail component and the SMTP server.

Here is some sample code

CREATE procedure sp_sendMail
@toAddress varchar(50),
@mailSubject varchar(100),
@mailBody varchar(500)
AS
BEGIN
declare @handle int
declare @object int
declare @fromAddress varchar(50)
declare @smtpServer varchar(255)

set @fromAddress = 'admin@siusic.com'
set @smtpServer = 'smtp.siusic.com'

--Create AspEmail object, set properties, and then call method
exec @handle = sp_OACreate 'Persits.MailSender', @object OUTPUT
exec @handle = sp_OASetProperty @object,'Host', @smtpServer
exec @handle = sp_OASetProperty @object,'From',@fromAddress
exec @handle = sp_OASetProperty @object,'Subject', @mailSubject
exec @handle = sp_OASetProperty @object,'Body', @mailBody
exec @handle = sp_OAMethod @object, 'AddAddress', Null, @toAddress, ''
exec @handle = sp_OAMethod @object,'Send'
exec @handle = sp_OADestroy @object
END

Maybe Microsoft realize it is too easy to get around SQL Mail and maybe too many customer request the SMTP mail functionality, now in SQL 2005 Microsoft provided a way to send mail using SMTP. It is called database mail. In SQL 2005 you don’t have to use AspEmail or other component to send SMTP mail. I guess that will hurt AspEmail quite a bit.

To use database mail you will have to enable database mail and set up a database mail profile. The following sample code is for that purpose.

-- Creates a new Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SiusicSystemMail',
@description = 'Mail account for use by all Siusic Reader.',
@email_address = 'admin@siusic.com',
@replyto_address = 'admin@siusic.com',
@display_name = ''admin@siusic.com',
@mailserver_name = 'smtp.siusic.com';

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SiusicSystemMail',
@description = 'Profile used for Siusic.';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ' SiusicSystemMail ',
@account_name = ' SiusicSystemMail ',
@sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'ReportingSvc',
@principal_name = 'public',
@is_default = 1 ;

After this configuration you can send mail by calling the system store procedure dbo.sp_send_dbmail

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = ' SiusicSystemMail',
@recipients = 'hchen@siusic.com',
@subject = = 'Test Email',
@body = 'Test Email'

Using SQL database mail has obvious benefit over SQL Mail. First, the mail delivering process runs out side of SQL server. SQL server just queue the mail for processing and it can continue to queue mail even the out side process is stopped. That increases reliability. Second, Database Mail provides background, or asynchronous, delivery and increase scalability. However SQL 2005 also provides SQL mail as a legacy component for backward compatibility.

Related Posts:

How to Restore a Database Used By Web Site 24/7?

T-SQL Query to Get Database Size

List All Permissions a User Has in SQL Server Database and Error 4064

The Best Way to Grant Execution Right on xp_cmdshell and Cross Database Ownership Chaining

The Best Way to Backup SQL Server