Atlanta Custom Software Development 

   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools


This stored procedure can be used to send an email from sql server stored proc.

Features :
  • SQL Mail is not required in order to send emails
  • You can specify your own smtp server name
  • Attach multiple files
  • Text or HTML email
  • To/from/cc/bcc/subject

Limitation :
  • Max body size 8000 characters


Note: If you need to send email with body size > 8000 characters then please refer to the following link

Stored procedure to Send SMTP mail from SQL SERVER 2000 (With very long email text, multiple attachments support)

Click here to copy the following block
/*
--example :

exec dbo.sp_SQLSMTPMail
       @from='support@binaryworld.no-ip.info'
       ,@to='abc@hotmail.com'
       ,@subject='<This is subject>'
       ,@body='<This is body>'
       ,@IsHTMLFormat=1
       ,@Attachments='c:\test.htm;c:\test.xml'        

*/

ALTER  Procedure dbo.sp_SQLSMTPMail
    @To      varchar(2048) = null,
    @Body     varchar(8000) = '',
    @Subject   varchar(255) = null,
    @Attachments varchar(1024) = null,
    @Query    varchar(8000) = null,
    @From     varchar(128) = null,
    @CC      varchar(2048) = '',
    @BCC     varchar(2048) = '',
    @IsHTMLFormat Bit = 0, -- HTML format or Plain Text Format [ Default is Text ]
    @SMTPServer  varchar(255) = 'localhost'-- put local network smtp server name here
    @cSendUsing  char(1)    = '2',
    @Port     varchar(3)  = '25',
    @cAuthenticate char(1)    = '0',
    @DSNOptions  varchar(2)  = '0',
    @Timeout   varchar(2)  = '30',
    @SenderName  varchar(128) = null,
    @ServerName  sysname    = null
As

/*******************************************************************/
--Name    : sp_SQLSMTPMail
--Server   : Generic
--Description : SQL smtp e-mail using CDOSYS, OLE Automation and a
--       network smtp server; For SQL Servers running on
--       windows 2000.
--
--Note    : Be sure to set the default for @SMTPServer above to 
--       the company network smtp server or you will have to 
--       pass it in each time.
--
--Comments  : Getting the network SMTP configured to work properly
--       may require engaging your company network or
--       server people who deal with the netowrk SMTP server.
--       Some errors that the stored proc returns relate to
--       incorrect permissions for the various SQL Servers to
--       use the SMTP relay server to bouce out going mail.
--       Without proper permissions the SQL server appears as
--       a spammer to the local SMTP network server.
--
--Parameters : See the 'Syntax' Print statements below or call the
--       sp with '?' as the first input.
--
--History   :
/*******************************************************************/

Set nocount on

-- Determine if the user requested syntax.
If @To = '?'
  Begin
   Print 'Syntax for sp_SQLSMTPMail (based on CDOSYS):'
   Print 'Exec master.dbo.sp_SQLSMTPMail'
   Print '   @To     (varchar(2048)) - Recipient e-mail address list separating each with a '';'' '
   Print '                    or a '',''. Use a ''?'' to return the syntax.'
   Print '   @Body    (varchar(8000)) - Text body; use embedded char(13) + char(10)'
   Print '                    for carriage returns. The default is nothing'
   Print '   @Subject   (varchar(255))) - E-mail subject. The default is a message from'
   Print '                    @@servername.'
   Print '   @Attachments (varchar(1024)) - Attachment list separating each with a '';''.'
   Print '                    The default is no attachments.'
   Print '   @Query    (varchar(8000)) - In-line query or a query file path; do not '
   Print '                    use double quotes within the query.'
   Print '   @From    (varchar(128)) - Sender list defaulted to @@ServerName.'
   Print '   @CC     (varchar(2048)) - CC list separating each with a '';'' or a '','''
   Print '                    The default is no CC addresses.'
   Print '   @BCC     (varchar(2048)) - Blind CC list separating each with a '';'' or a '','''
   Print '                    The default is no BCC addresses.'
   Print '   @IsHTMLFormat (Bit) - If 1 then Format of Mail will be HTML Mail otherwise Plain text'
   Print '   @SMTPServer (varchar(255)) - Network smtp server defaulted to your companies network'
   Print '                    smtp server. Set this in the stored proc code.'
   Print '   @cSendUsing  (char(1))    - Specifies the smpt server method, local or network. The'
   Print '                    default is network, a value of ''2''.'
   Print '   @Port    (varchar(3))  - The smtp server communication port defaulted to ''25''.'
   Print '   @cAuthenticate (char(1))    - The smtp server authentication method defaulted to '
   Print '                    anonymous, a value of ''0''.'
   Print '   @DSNOptions (varchar(2))  - The smtp server delivery status defaulted to none,'
   Print '                    a value of ''0''.'
   Print '   @Timeout   (varchar(2))  - The smtp server connection timeout defaulted to 30 seconds.'
   Print '   @SenderName (varchar(128)) - Primary sender name defaulted to @@ServerName.'
   Print '   @ServerName (sysname)    - SQL Server to which the query is directed defaulted'
   Print '                    to @@ServerName.'
   Print ''
   Print ''
   Print 'Example:'
   Print 'sp_SQLSMTPMail ''<user@mycompany.com>'', ''This is a test'', @SMTPServer = <network smtp relay server>'
   Print ''
   Print 'The above example will send an smpt e-mail to <user@mycompany.com> from @@ServerName'
   Print 'with a subject of ''Message from SQL Server <@@ServerName>'' and a'
   Print 'text body of ''This is a test'' using the network smtp server specified.'
   Print 'See the MSDN online library, Messaging and Collaboration, at '
   Print 'http://www.msdn.microsoft.com/library/ for details about CDOSYS.'
   Print 'subheadings: Messaging and Collaboration>Collaboration Data Objects>CDO for Windows 2000>'
   Print 'Reference>Fields>http://schemas.microsoft.com/cdo/configuration/>smtpserver field'
   Print ''
   Print 'Be sure to set the default for @SMTPServer before compiling this stored procedure.'
   Print ''
   Return
  End


-- Declare variables
Declare @iMessageObjId  int
Declare @iHr       int
Declare @iRtn       int
Declare @iFileExists   tinyint
Declare @Cmd      varchar(255)
Declare @QueryOutPath  varchar(50)
Declare @dtDatetime    datetime
Declare @ErrMssg    varchar(255)
Declare @Attachment   varchar(1024)
Declare @iPos       int
Declare @ErrSource   varchar(255)
Declare @ErrDescription varchar(255)

-- Set local variables.
Set @dtDatetime = getdate()
Set @iHr = 0

-- Check for minimum parameters.
If @To is null
  Begin
   Set @ErrMssg = 'You must supply at least 1 recipient.'
   Goto ErrMssg
  End

-- 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, ';', ',')

-- Set the default SQL Server to the local SQL Server if one 
-- is not provided to accommodate instances in SQL 2000.
If @ServerName is null
  Set @ServerName = @@servername

-- Set a default "subject" if one is not provided.
If @Subject is null
  Set @Subject = 'Message from SQL Server ' + @ServerName

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

-- Set a default "sender name" if one is not provided.
If @SenderName is null
  Set @SenderName = 'SQL-' + Replace(@ServerName,'\','_')

-- Create the SMTP message object.
EXEC @iHr = sp_OACreate 'CDO.Message', @iMessageObjId OUT
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error creating object CDO.Message.'
   Goto ErrMssg
  End

-- Set SMTP message object parameters.
-- To
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'To', @To
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message parameter "To".'
   Goto ErrMssg
  End

-- Subject
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Subject', @Subject
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message parameter "Subject".'
   Goto ErrMssg
  End

-- From
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'From', @From
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message parameter "From".'
   Goto ErrMssg
  End

-- CC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'CC', @CC
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message parameter "CC".'
   Goto ErrMssg
  End

-- BCC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'BCC', @BCC
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message parameter "BCC".'
   Goto ErrMssg
  End

-- DSNOptions
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'DSNOptions', @DSNOptions
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message parameter "DSNOptions".'
   Goto ErrMssg
  End

-- Sender
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @SenderName
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message parameter "Sender".'
   Goto ErrMssg
  End


-- Is there a query to run?
If @Query is not null and @Query <> ''
  Begin
   -- We have a query result to include; temporarily send the output to the
   -- drive with the most free space. Use xp_fixeddrives to determine this.
   -- If a temp table exists with the following name drop it.
   If (Select object_id('tempdb.dbo.#fixeddrives')) > 0
     Exec ('Drop table #fixeddrives')
   
   -- Create a temp table to work with xp_fixeddrives.
   Create table #fixeddrives(
       Drive char(1) null,
       FreeSpace varchar(15) null)

   -- Get the fixeddrive info.
   Insert into #fixeddrives Exec master.dbo.xp_fixeddrives

   -- Get the drive letter of the drive with the most free space
   -- Note: The OSQL output file name must be unique for each call within the same session.
   --    Apparently OSQL does not release its lock on the first file created until the session ends.
   --    Hence this alleviates a problem with queries from multiple calls in a cursor or other loop.
   Select @QueryOutPath = Drive + ':\TempQueryOut' +
                ltrim(str(datepart(hh,getdate()))) +
                ltrim(str(datepart(mi,getdate()))) +
                ltrim(str(datepart(ss,getdate()))) +
                ltrim(str(datepart(ms,getdate()))) + '.txt'
    from #fixeddrives
    where FreeSpace = (select max(FreeSpace) from #fixeddrives )
   
   -- Check for a pattern of '\\*\' or '?:\'.
   -- If found assume the query is a file path.
   If Left(@Query, 35) like '\\%\%' or Left(@Query, 5) like '_:\%'
     Begin
      Select @Cmd = 'osql /S' + @ServerName + ' /E /i' +
              convert(varchar(1024),@Query) +
              ' /o' + @QueryOutPath + ' -n -w5000 '
     End
   Else
     Begin
      Select @Cmd = 'osql /S' + @ServerName + ' /E /Q"' + @Query +
              '" /o' + @QueryOutPath + ' -n -w5000 '
     End

   -- Execute the query
   Exec master.dbo.xp_cmdshell @Cmd, no_output

   -- Add the query results as an attachment if the file was successfully created.
   -- Check to see if the file exists. Use xp_fileexist to determine this.
   -- If a temp table exists with the following name drop it.
   If (Select object_id('tempdb.dbo.#fileexists')) > 0
     Exec ('Drop table #fileexists')
   
   -- Create a temp table to work with xp_fileexist.
   Create table #fileexists(
       FileExists tinyint null,
       FileIsDirectory tinyint null,
       ParentDirectoryExists tinyint null)

   -- Execute xp_fileexist
   Insert into #fileexists exec master.dbo.xp_fileexist @QueryOutPath

   -- Now see if we need to add the file as an attachment
   If (select FileExists from #fileexists) = 1
     Begin
      -- Set a variable for later use to delete the file.
      Select @iFileExists = 1

      -- Add the file path to the attachment variable.
      If @Attachments is null
        Select @Attachments = @QueryOutPath
      Else
        Select @Attachments = @Attachments + '; ' + @QueryOutPath
     End
  End

-- Check for multiple attachments separated by a semi-colon ';'.
If @Attachments is not null
  Begin
   If right(@Attachments,1) <> ';'
     Select @Attachments = @Attachments + '; '
   Select @iPos = CharIndex(';', @Attachments, 1)
   While @iPos > 0
     Begin
      Select @Attachment = ltrim(rtrim(substring(@Attachments, 1, @iPos -1)))
      Select @Attachments = substring(@Attachments, @iPos + 1, Len(@Attachments)-@iPos)
      EXEC @iHr = sp_OAMethod @iMessageObjId, 'AddAttachment', @iRtn Out, @Attachment
      IF @iHr <> 0
        Begin
         EXEC sp_OAGetErrorInfo @iMessageObjId, @ErrSource Out, @ErrDescription Out
         Select @Body = @Body + char(13) + char(10) + char(13) + char(10) +
                  char(13) + char(10) + 'Error adding attachment: ' +
                  char(13) + char(10) + @ErrSource + char(13) + char(10) +
                  @Attachment
        End
      Select @iPos = CharIndex(';', @Attachments, 1)
     End
  End


--HTMLBody
if @IsHTMLFormat=1
begin
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'HTMLBody', @Body
   IF @iHr <> 0
     Begin
      Set @ErrMssg = 'Error setting Message parameter "BodyFormat".'
      Goto ErrMssg
     End
end
else
begin
   -- TextBody
   EXEC @iHr = sp_OASetProperty @iMessageObjId, 'TextBody', @Body
   IF @iHr <> 0
     Begin
      Set @ErrMssg = 'Error setting Message parameter "TextBody".'
      Goto ErrMssg
     End
end

-- Other Message parameters for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MimeFormatted', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'AutoGenerateTextBody', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MDNRequested', True

-- Set SMTP Message configuration property values.
-- Network SMTP Server location
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
@SMTPServer
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message configuraton field "smtpserver".'
   Goto ErrMssg
  End

-- Sendusing
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
@cSendUsing
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message configuraton field "sendusing".'
   Goto ErrMssg
  End

-- SMTPConnectionTimeout
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout").Value',
@Timeout
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message configuraton field "SMTPConnectionTimeout".'
   Goto ErrMssg
  End

-- SMTPServerPort
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPServerPort").Value',
@Port
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message configuraton field "SMTPServerPort".'
   Goto ErrMssg
  End

-- SMTPAuthenticate
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate").Value',
@cAuthenticate
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error setting Message configuraton field "SMTPAuthenticate".'
   Goto ErrMssg
  End

-- Other Message Configuration fields for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL").Value',True

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/LanguageCode").Value','en'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendEmailAddress").Value', 'Test User'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value',null

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value',null

-- Update the Message object fields and configuration fields.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update'
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error updating Message configuration fields.'
   Goto ErrMssg
  End

EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update'
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error updating Message parameters.'
   Goto ErrMssg
  End

-- Send the message.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Send'
IF @iHr <> 0
  Begin
   Set @ErrMssg = 'Error Sending e-mail.'
   Goto ErrMssg
  End
Else
  Print 'Mail sent.'

Cleanup:
  -- Destroy the object and return.
  EXEC @iHr = sp_OADestroy @iMessageObjId
  --EXEC @iHr = sp_OAStop

  -- Delete the query output file if one exists.
  If @iFileExists = 1
   Begin
     Select @Cmd = 'del ' + @QueryOutPath
     Exec master.dbo.xp_cmdshell @Cmd, no_output
   End
  Return

ErrMssg:
  Begin
   Print @ErrMssg
   If @iHr <> 0
     Begin
      EXEC sp_OAGetErrorInfo @iMessageObjId, @ErrSource Out, @ErrDescription Out
      Print @ErrSource
      Print @ErrDescription
     End

   -- Determine whether to exist or go to Cleanup.
   If @ErrMssg = 'Error creating object CDO.Message.'
     Return
   Else
     Goto Cleanup
  End

GO


Audit, Notify, Deploy and Manage SSIS
Download the Free 30-day Trial Version...Learn More About This Product...

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )

Recommanded Links

 


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  Blogs

© 2008 BinaryWorld LLC. All rights reserved.