Thursday, October 18, 2007

How to send Mail From Sql


This is a very common situation when it is required to send mails. There are so many ways to send mail.

The very common is Sql mail. Through Sql server 2005 you can send mail.

There are two methods available
1.DataBase Mail
2. Thorugh OLE

Here is the Stored Procedure to send mail through OLE.

Just copy this prodedure and run it in your database...

--WSA_sp_SendEmail 'virendra@mailserver.local','virendra@mailserver.local','Hi','Test Mail From CDO'
CREATE PROCEDURE [dbo].[WSA_sp_SendEmail]
@From varchar(100),
@To varchar(1000),
@Subject varchar(250),
@Body varchar(max),
@CC varchar(1000) = null
AS
BEGIN
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
Print @From
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '10.0.0.100'
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
--Print @To
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
if (@Cc <> '')
EXEC @hr = sp_OASetProperty @iMsg, 'Cc', @Cc
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'HtmlBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0
select @hr
--print @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
Print @hr
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT 'hi'
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
EXEC @hr = sp_OADestroy @iMsg
END

*Please change the SMTP server address in the Procedure. Currently it is set to 10.0.0.100, Change this address according to your SMTP server Address.

To send the mail one have to "Enable OLE Automation".

Go to Sql server source configuration area and enable OLE Automation from the options list.

Sql server Source Configuration area is under Start->Microsoft Sql Server-> Configuaration Tools..

Enjoy Mailing


2 comments:

praveen said...

Please help how to enable OLE Automation from the options list.Explain clearly please......

Chandrashekhar Mahale said...

Hi Praveen,


Start->Microsoft Sql Server-> Configuaration Tools -->SQL Server Surface Area Configuaration
Check "Enable OLE Automation" check box and apply settings.

Chandrashekhar

back to top