DB Mail Configuration

SQL developers can send e-mail from SQL Server using CDONTS or CDOSYS.
Although SQL Server 2005 introduced Database Mail for sending emails from SQL Server, the old school is still common among SQL Server administrators and t-sql developers.
Let's make an example and remember how CDONTS can be used along with SQL Server OLE Automation stored procedures.
Microsoft SQL Server 2005 has new tools for SQL Server database administrators and for SQL developers to send email from SQL Server.
SQL Server 2005 has introduced SQL Server Database Mail with SMTP support for enabling developers send emails using t-sql code.
The only requirement for Database Mail in SQL Server 2005 or SQL Server 2008 is an SMTP server.
After you configure Database Mail in SQL Server, t-sql developers can use sp_send_dbmail system stored procedure to send e-mail from SQL Server.

DECLARE @to varchar(max)
DECLARE @cc varchar(max)
DECLARE @bcc varchar(max)
DECLARE @from varchar(max)
DECLARE @subject nvarchar(max)
DECLARE @body nvarchar(max)
DECLARE @mailid int
DECLARE @hr int
SET @to = 'sql.developer@vairagi.com'
SET @cc = 'sql.administrator@vairagi.com'
SET @bcc = 'database.admin@vairagi.com'
SET @from ='sql.user@vairagi.com'
SET @subject = 'This is Test e-Mail Subject for SQL Server CDONTS mail'
SET @body = 'This email is send for test purpose.'
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @mailid OUT
EXEC @hr = sp_OASetProperty @mailid, 'From', @from
EXEC @hr = sp_OASetProperty @mailid, 'Body', @body
EXEC @hr = sp_OASetProperty @mailid, 'MailFormat', 0
EXEC @hr = sp_OASetProperty @mailid, 'BodyFormat', 0
EXEC @hr = sp_OASetProperty @mailid, 'BCC',@bcc
EXEC @hr = sp_OASetProperty @mailid, 'CC', @cc
EXEC @hr = sp_OASetProperty @mailid, 'Subject', @subject
EXEC @hr = sp_OASetProperty @mailid, 'To', @to
EXEC @hr = sp_OAMethod @mailid, 'Send', NULL
EXEC @hr = sp_OADestroy @mailid

if you get the following errors when you execute the SQL mail send script given in this t-sql tutorial, follow the solution steps in order.

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
--Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
--SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

EXEC @hr = sp_OASetProperty @MailID, 'From',@kimden
--Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1
--SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
--Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
--SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

Solution:
exec sp_configure
go
exec sp_configure 'Ole Automation Procedures', 1
--Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go

When you execute the above t-sql configuration script, you may get the following error message that is pointing to configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.
--Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
--The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.

The above error message is actually not because of 'Ole Automation Procedures' configuration option does not exist, it is beacause an SQL Server configuration advanced option.
So sql administrators should first set the 'show advanced options' configuration option before trying to change 'Ole Automation Procedures' option.

First run the below script :

exec sp_configure 'show advanced options', 1
--Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go

Then execute the following script once more :

exec sp_configure
go
exec sp_configure 'Ole Automation Procedures', 1
--Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go