SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


Send SQL Mail using CDONTS.NewMail in SQL Server 2008


Summary :

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@kodyaz.com'
SET @cc = 'sql.administrator@kodyaz.com'
SET @bcc = 'database.admin@kodyaz.com'
SET @from ='sql.user@kodyaz.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
Code


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.
Code

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.
Code

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.
Code

EXEC @hr = sp_OADestroy @MailID
--Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
--SQL Server blocked access to procedure 'sys.sp_OADestroy' 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.
Code

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
Code

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
Code

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
Code


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.