public knowledge blog_load(sender Virendra)

                                                                                                                          Nothing shocks me, I am a Software Engineer.

Send mail From SQL server

Posted by Virendra Dugar on January 22, 2009

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

One way is to send mail through Sql server 2005.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: