January 31, 2006 at 2:58 am
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
January 31, 2006 at 9:41 am
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!
February 1, 2006 at 4:00 am
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).
February 1, 2006 at 10:09 am
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.
February 1, 2006 at 10:27 am
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
February 2, 2006 at 7:04 am
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
February 2, 2006 at 7:16 am
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
February 2, 2006 at 7:29 am
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