November 12, 2002 at 6:02 pm
Hope you can help me with this.
One ASP developer has a question for me. He wants to send out an HTML email to all customers that have policy expired today. He could do it using CDONT on a separate IIS box but someone must open the page and click the button to send the email. He prefers to have it scheduled to send mail out at 1:00 am. He couldn't schedule it in ASP. I could do it in SQL: create a temp table with the emails of expired policy holders, run XP_SENDMAIL. However these a re my concerns:
* Is it true that you can't schedule to do CDONT or a specific task everyday in ASP?
* Beside SQLMail, what can I use? I heard SMTP: is there documents for it?
* Can XP_SENDMAIL send HTML email?
Thx.
November 12, 2002 at 7:23 pm
(1) He should be able to write a VBScript routine and schedule it using the Windows Task Scheduler. Not through ASP. Instead create a script file such as MailCustomers.vbs or the like. Here is a brief intro to the Task Scheduler:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnw2k/html/tasksched.asp
(2) You could use ActiveState Perl and Mail::Sender or Net::SMTP neither of which require the SMTP server to be on the same server as where the script is running. This job could be scheduled through SQL Agent. (My preferred method, only because I like Perl solutions... #1 is probably best unless you've got Perl fanatics in your shop)
(3) You can use a 3rd party product like ASPMail (the one that turns up most in queries... I've never used it since I rely on Perl).
xp_sendmail doesn't support HTML emails natively. There is a KB article detailing alternative methods for sending mail from SQL Server, but currently it is unavailable from the MS site. This is where it was:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;312839
Perhaps the link will start working again.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 13, 2002 at 7:35 am
I have some SQL Agent jobs that use CDONTS. Although this works because CDONTS is installed on our SQL box, because this particular box has both IIS, and SQL. It is my understanding that you can install CDONTS on a standalone SQL Box, although I have not done that.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 7:36 am
I have some SQL Agent jobs that use CDONTS. Although this works because CDONTS is installed on our SQL box, because this particular box has both IIS, and SQL. It is my understanding that you can install CDONTS on a standalone SQL Box, although I have not done that.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 14, 2002 at 3:52 pm
You can also use SQL Server DTS with ActiveX Script Task to send SMTP email and scheduled the DTS package. Following code shows an ActiveX Script Task to send email using CDONTS.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Set myMail = CreateObject("CDONTS.NewMail")
myMail.From = "god@heaven.com"
myMail.To = "lucifer@hell.com"
myMail.cc = "humans@earth.com"
myMail.Subject = "Always welcome"
myMail.Body = "bla bla bla ...."
myMail.Send
Set myMail = Nothing
Main = DTSTaskExecResult_Success
End Function
Cheers!
November 14, 2002 at 4:15 pm
If you have IIS and SQL Server installed on separate servers, you won't be able to use CDONTS from with SQL Server. CDONTS requires a local SMTP server. You might want to use CDOSYS instead and specify a remote SMTP server
November 14, 2002 at 5:18 pm
I send HTML mail using XP_Sendmail every day. Your Text message is the HTML code itself, add any pictures or sound you include as attatchments to the mail or in a publicly accessable space, and go to town.....
We dynamically build the HTML pages for the mail and pass the text in as a series of variables, and can do mass mailings that cause the exchange guys teeth to grind......
November 14, 2002 at 5:24 pm
I would agree with Brian on this. You can have a Script file, either with a .vbs extension for VBScript or .js for JavaScript and use Scheduler to Schedule (Win 2000 or Winat for NT) it from the IIS Box.
You can find some info here
http://www.iisfaq.com/?Search=cdonts
Best Regards,
Trevor Benedict R
Microsoft Certified Professional (VB)
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
November 18, 2002 at 4:39 am
We do large numbers of automated emails from our workflow application. Our scheduled DTS jobs use Javascript and XMLHTTP to call asp pages on the webserver. The ASP pages then query what needs sending (SP call back to the database server) and use CDONTS to send the emails. We use to use CDONTS in the DTS job directly but found it very difficult to error trap within the DTS.
November 18, 2002 at 12:30 pm
I agree with bkelly on suggestion number 1- writing a VBScript. This is how we generate static web pages (ultimately by calling a function that was residing on an .ASP page). Very quick and simple, and didn't require much rewriting.
This is the best idea because you can probably reuse the code in the existing page and it keeps the process where it belongs (outside of the SQL Server.)
CDONTS will work with a remote SMTP server. Make note of the following
CdoConfiguration.cdoSendUsingMethod
CdoConfiguration.cdoSMTPServer
if cdoSendUsingMethod is 1 then you need to have a local smtp service.
if cdoSendUsingMethod is 2 then emails will be sent on the SMTP port over the local network to the server specified in:
CdoConfiguration.cdoSMTPServer
This isn't the perfect link, but msdn as usual has great reference on this;
I use CDONTS at work, and send my mail through our external exchange server. I am hesitant to start SMTP locally on a win2k box because I don't yet know how to prevent anyone from turning my sql servers into SPAM servers. My external exchange server is secure and doesn't allow mail relaying (I can't email people outside the company from my sqlservers)
-Ken
November 18, 2002 at 12:42 pm
quote:
I am hesitant to start SMTP locally on a win2k box because I don't yet know how to prevent anyone from turning my sql servers into SPAM servers.
One of the easiest ways to set the access and relay controls such that only the localhost IP address (127.0.0.1) and whatever the static IP address of the SQL Server is as the only valid computers that can access the SMTP Server.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 18, 2002 at 12:42 pm
thx guys.
will pass this to the developers.
November 18, 2002 at 10:54 pm
Hi all. I need to configure SQL mail on a SQL 2K on an Advanced Server 2000. I need some help with the steps involved in setting up SQL Server Mail. Please bare with me. It's one of my weakest areas. I need to know all the steps to accomplish this.
Thank you in advance.
November 19, 2002 at 2:07 am
Prerequisite: The SQL Server Agent service must be running on the Windows login Account with access to the mail profile set up on the Machine.
A Mail Profile needs to be set on the Server with permissions to the Windows logon Account.
Select Services from Start Menu à Programsà Administrative Tools and change the setting for the SQL Server Agent service and change this account text field under LOGON tab.
Once this is done go to SQL Server Agent Properties under Management in SQL EM and enter the mail profile name setup on the pc and test it...
I hope this is sufficient for the same...
Arvind
Arvind
November 21, 2002 at 3:30 am
A variation on a theme here but when faced with a similar problem where I wanted to send a scheduled email and to make use of many existing library functions, some COM+ some ASP written to be called from an ASP web page via IIS I opted for a different approach.
I used a VBScript started via the NT Scheduler. The script in this case however opens the ASP page, discards it's contents, leaving the ASP script itself to send the email using a custom COM+ component.
Here's the script..
'
' AutoEmail.vbs - Open a web page 'autoemail.asp', so as to run it's script but discard it's contents
'
' Keith Talbot - Apr 2002
'
' This script is run automatically by the Windows NT Scheduler
'
Option Explicit
Dim oShell, oHTTP
Dim sPage
'
' Determine the ASP script to run based on the server
'
Set oShell = CreateObject("WScript.Shell")
Select Case oShell.Environment("PROCESS")("COMPUTERNAME")
Case "DEVSERVER"
sPage = "http://x.x.x.x/dev/asptest/autoemail.asp"
Case "LIVESERVER"
sPage = "http://x.x.x.x/autoemail.asp"
Case Else
sPage = ""
WScript.Echo "Server name '" & oShell.Environment("PROCESS")("COMPUTERNAME") & "' is not recognised."
End Select
Set oShell = Nothing
'
' Use HTTP GET to call the script which will generate the emails.
' Uses XMLDOM as a convenient way to do this.
'
If sPage <> "" Then
Set oHTTP = CreateObject("Microsoft.XMLHTTP")
oHTTP.Open "GET", sPage, False
oHTTP.Send ""
If oHTTP.Status <> 200 Then
WScript.Echo "HTTP GET Status: " & oHTTP.Status & " " & oHTTP.StatusText
End If
Set oHTTP = Nothing
End If
Edited by - ktalbot on 11/21/2002 03:32:39 AM
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply