Binaryworld - Stored procedure to Send SMTP mail from SQL SERVER 2000 (With very lon ... [ SQL Server -> Stored Procedure ]

This article will show you how to use CDOSYS library (Provided with most of windows versions including win 2000, xp, 2003) to send mails from sql server.

I have created a COM component so you can send emails with long body text.

Click here to copy the following block

use master

go

/*

Copyright © 2005 Nayan Patel. All rights reserved.

Author : Nayan Patel
Created On : 9/7/05
Site  : www.binaryworld.net

Description : This stored proc can be used if you dont have SQLMail setup. SQLMail has several requirements and limitations.
  You can use this stored proc to send emails from sql server. You can also send multiple attachment files in one email using this sp.

Installation :
============================
- This SP requires SMTPMail.dll (free dll provided by Binaryworld)
- Extract files privided in a zip file including SMTPMail.dll to some folder (e.g. C:\MyComponents)
- Goto Start->Run and type the following command to register the SMTPMail.dll (or you can run the register.bat file provided in the zip file)
regsvr32 c:\MyComponents\SMTPMail.dll
- Hit enter and you will get message that registered successfully
- Now open query analyzer and run this entire script which will create sp_SQLSMTPMail in master database.

Uninstall :
============================
- Goto Start->Run and type the following command to register the DLL (or you can run the UnRegister.bat file provided in the zip file)
regsvr32 /u c:\MyComponents\SMTPMail.dll
- Hit enter and you will get message that un-registered successfully

example :
============================

exec dbo.sp_SQLSMTPMail
@from='someone@binaryworld.net'
,@to='mom@yahoo.com'
,@cc='dad@hotmail.com'
,@subject='<This is subject>'
,@body='This is <b>Binaryworld</b> Email'
,@IsHTMLFormat=1
,@Attachments='c:\test1.txt;c:\test2.txt'
   ,@SMTPServer=''
*/

Create Procedure dbo.sp_SQLSMTPMail
    @To      varchar(2048) = NULL,
    @Body     text = NULL,
    @Subject   varchar(255) = NULL,
    @From     varchar(128) = NULL,
    @CC      varchar(2048) = NULL,
    @BCC     varchar(2048) = NULL,
    @Attachments varchar(8000)=NULL,
    @IsHTMLFormat Bit = 0, -- HTML format or Plain Text Format [ Default is Text ]
--    @RequestReceipt Bit=0,
    @SMTPServer varchar(255) = '' -- (e.g. mail.bellsouth.net) put your smtp server name. SMTP server can be your local SMTP server or any Network/ISP SMTP server.
As

DECLARE @object int
DECLARE @hr int
Declare @ErrMssg  varchar(255)
Declare @Attachment varchar(1024)
Declare @iPos int
Declare @iRtn int
Declare @ErrSource varchar(255)
Declare @ErrDescription varchar(255)

-- CDOSYS uses commas to separate recipients. Allow users to use
-- either a comma or a semi-colon by replacing semi-colons in the
-- To, CCs and BCCs.
Select @To = Replace(@To, ';', ',')
Select @CC = Replace(@CC, ';', ',')
Select @BCC = Replace(@BCC, ';', ',')

-- Check for minimum parameters.
If @To is null
Begin
RAISERROR('You must supply at least 1 recipient.',16,1)
Return
End

-- Set a default "from" if one is not provided.
If @From is null
Set @From = 'SQL-' + Replace(@@servername,'\','_')

EXEC @hr = sp_OACreate 'SMTPMail.Mail', @object OUT
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'MailFrom',@From
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'MailBody',@Body
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'MailSubject',@Subject
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'MailTo', @To
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'MailCC', @CC
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'MailBCC', @BCC
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

--EXEC @hr = sp_OASetProperty @object, 'RequestReceipt', @RequestReceipt
EXEC @hr = sp_OASetProperty @object, 'IsBodyHTML', @IsHTMLFormat
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'SMTPServer', @SMTPServer
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OASetProperty @object, 'AttachFiles', @Attachments
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

EXEC @hr = sp_OAMethod @object, 'Send', NULL
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
END
Else
Print 'Mail sent to ' + @To

Cleanup:
-- Destroy the object and return.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @object, @ErrSource Out, @ErrDescription Out
Print @ErrSource
Print @ErrDescription
Return
END

Return

GO