Send Emails without using MS Exchange

  • Is there anyway to send email within DTS Packages or Query Analyzer without being dependent on the MS Exchange Server? Please provide an example. We have SQL Server 2000 (SP3) on Windows 2k.

    Thanks in advance, Kevin

  • Your 2000 box has a CDONTS library that will use your default email system.  Here's the code we've used multiple times.  Use it in an ActiveX task and set your global variables for the addresses.

    ********************

    Function Main()

    Dim strMsg 'Error message

    Dim objMail 'Mail object

    set objMail = CreateObject("CDONTS.NewMail")

    objMail.To = DTSGlobalVariables("gv_mailto").Value

    objMail.From = DTSGlobalVariables("gv_mailfrom").Value

    objMail.Subject = "Some Subject"

    objMail.Body = "Message to Send"

    objMail.Send

    Set objMail = Nothing

    Main = DTSTaskExecResult_Success

    End Function

    ***************************

    Hope it helps!

  • 1)

    http://www.sqldev.net/dts/SMTPTask.htm

     The SMTP Mail Task provides a MAPI free alternative to the existing DTS Send Mail Task; it provides equivalent functionality without having to rely on MAPI or the requirement to have Microsoft Outlook installed. The SMTP Mail Task provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as a DTS Custom Task. It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail requests. The SMTP Mail Task is using TCP/IP sockets to communicate to port 25.

    2)

    http://www.sqldev.net/xp/xpsmtp.htm

     XPSMTP provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as an Extended Stored Procedure.

    It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail request. XPSMTP is using TCP/IP sockets to communicate to port 25. XPSMTP does not spawn additional threads, the xp_smtp_sendmail request is handled on the same thread as it is being called on by SQL Server. Each call to xp_smtp_sendmail establishes a connection to the SMTP server and disconnects when done sending the email. The connection is created using asynchronous communication and aborts based on a timeout value (@timeout which by default is 10000 milliseconds, so 10 seconds).

  • I'm sorry but installing a dll on the SQL server qualifies as installing software to me.  Am I wrong?  However the smtp method is the least intrusive, you only need to install a single DLL and then create an extended stored procedure.  After that it's easy.

     


    Kindest Regards,

    Scott Beckstead

    "We cannot defend freedom abroad by abandoning it here at home!"
    Edward R. Murrow

    scottbeckstead.com

  • Technically, this would count as installing something, but it's more of an "extract this DLL to this folder and register it as an extended proc in SQL" process. After that, it's painless to use, though I'd recommend writing a wrapper proc that provides most of your default values. It works really well for us, but doesn't offer the ability to pass a query and send the results via SMTP. Have to gen a file first. 🙁 DB Mail in 2005 helps some, but can't receive mail the way SQL Mail can - minor limitation for most people from what I've seen.

    -Pete

  • Another alternative that I am currently using is CDO for Windows (CDOSYS.dll). I found this to be the best solution since CDONTS does not work for Windows Server 2003 (Important to remember if you plan to upgrade your server). This is referenced in Microsoft article:

    http://support.microsoft.com/kb/312839/

    Here is a forum post I submitted in reference to using CDOSYS as a stored proc. If you would like the full code I use for the stored proc, PM me:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=234982

    Code snippet:


    EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing".Value','2'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver".Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServer
    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
    EXEC @hr = sp_OASetProperty @iMsg, 'Fields("urn:schemas:mailheader:priority").Value','High'
    EXEC @hr = sp_OAMethod @iMsg, 'Fields.Update', null
    EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
    EXEC @hr = sp_OASetProperty @iMsg, 'From',@From
    EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
    EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @TextBody
    EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
    EXEC @hr=sp_OADestroy @iMsg

  • CDOSys is an option, but I had issues with the sp_OA procs when trying to use them for running DTS on demand. They stopped working - no warning, no errors. One day they worked fine, the next they did nothing. Powerful alternative if you can get it working for you, though. It allows you to do a lot of neat things that the SMTPMail XP doesn't.

    -Pete

  • In my experience, if the stored proc is functioning correctly with no errors, then the problem has turned out to be a problem with the SMTP server. Usually ends up being a queuing/delivery issue.

    Good luck with whatever you decide to use

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply