Send Emails without using MS Exchange

Question

Send Emails without using MS Exchange

HookSQLDBA7

SSChampion

Points: 10555
More actions
January 31, 2006 at 2:58 am

#67136

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

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

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

dbunn Valued Member Points: 62 More actions · Answer 1

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!

maggush Grasshopper Points: 10 More actions · Answer 2

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

Scott Beckstead Right there with Babe Points: 780 More actions · Answer 3

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

Peter Schott SSCrazy Eights Points: 9657 More actions · Answer 4

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

Don Staten SSC Eights! Points: 957 More actions · Answer 5

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://qa.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

Peter Schott SSCrazy Eights Points: 9657 More actions · Answer 6

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

Don Staten SSC Eights! Points: 957 More actions · Answer 7

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