The sales manager of the Northwind Company wishes to receive an E-mail indicating all orders above $10,000 on a daily basis. The sales manager would like to thank the customers who placed these orders by calling them. The body of E-mail should include all the necessary information, so there should not be any need to access the company’s client-server application. The first step is to delegate as much processing as possible to our database server. In my example I selected the standard Northwind database that is installed with the SQL Server 7.0.
I decided to create a view in the Northwind database that would give me all that I need. Start the SQL Server Enterprise Manager, and check out the list of views under Northwind database. The "Order Subtotals" view is handy because it calculates the subtotals of all orders placed with Northwind. I decided to use this view in my new view, "10K_Order_Qry,"’ as follows:
CREATE VIEW dbo.[10K_Order_Qry]
AS
SELECT [order subtotals].OrderID, CONVERT(varchar(15),
[order subtotals].Subtotal) AS 'Subtotal', CONVERT(char(10),
Orders.OrderDate, 110) AS 'OrderDate',
Customers.CompanyName, Customers.ContactName,
Customers.Country, Customers.Phone
FROM [order subtotals], Orders, Customers
WHERE [order subtotals].OrderID = Orders.OrderID AND
Orders.CustomerID = Customers.CustomerID AND
subtotal >= 10000
-- Below lines can be switched in order to look for today's sales over $10,000
-- (comment the next line, and uncomment the second line)
AND OrderDate >= '02-01-1998' AND OrderDate <= '02-28-1998'
--AND OrderDate = convert(char(10),getdate(),110)
Although it is not a good idea to use a view within a view, I used "Order Subtotals" view in "10K_Order_Qry" because, well …, how to write a good view is not the aim of this article!
In order not to modify the Northwind data and still have something to show in our E-mail, please review the last two lines of our new view, "10K_Order_Qry." The comment lines, above the last two lines, explain our final goal. I am compelled to point out that we could have written a stored procedure in order to achieve a better performance. Since a stored procedure can be compiled, an execution plan for SQL Server will greatly reduce the overhead of figuring out the plan every time we execute this view.
To verify that we have all the fields for our report, we can execute this view in the SQL Server Query Analyzer.
Windows Scripting Host
All of us, at some point, used scripting in MS-DOS batch files, the only native scripting language in the previous Windows operating systems. The MicrosoftWindows Scripting Host integrated into Windows 98, Internet Information Server 4.0, Windows NT Workstation 2000, and Windows NT Server 2000 is a language-independent scripting host. The Visual Basic and JavaScript scripting engines are included in the Windows Scripting Host, and other software companies, if they have not yet, will provide us with other ActiveX scripting engines for other languages.
Essentially what the Windows Scripting Host enables us to do is to execute scripts directly on the Windows desktop, or from within the command console, without any need to run these scripts within the HTML documents or Active Server Pages. This is powerful, and you should explore it more, if you have needs for logon scripting, administrative scripting, or machine automation.
Now that we are a little familiar with the Windows Scripting Host (WSH) – talk about an understatement! – we are ready to write the script that builds the report and E-mails it to our service-conscious Northwind Manager. I have decided to write this script in the Visual Basic Scripting language. You may use any text editor to create this script file. The only requirement is to save the file with the "vbs" extension, as "MyScript.vbs." If you install Windows NT Option Pack 4, there is a script debugger, MSSCRDBG.EXE, which can be used to create and debug this script. It beats "Visual" Notepad!
Dim objSendMail
Dim strTo, strFrom
Dim strSubject, strBody
Dim shipUic
' mail constants
Const CdoBodyFormatType = 0 ' Body property is HTML
Const CdoMailFormatType = 0 ' NewMail object is in MIME format
Const CdoNormal = 1 ' Normal importance (default)
strFrom = "admin@northwind.com" ' System administrator or DBA mail account
strTo =" manager@northwind.com" ' Recipient mail account - i.e. Sales Manager
strSubject = "Sales over $10,000" ' Mail subject
' Call function to build the HTML mail body
strBody = MailBody()
' The following section creates the E-mail object and sends the mail
Set objSendMail = CreateObject("CDONTS.NewMail")
objSendMail.From = strFrom
objSendMail.To = strTo
objSendMail.Subject = strSubject
objSendMail.Body = strBody
objSendMail.BodyFormat = CdoBodyFormatType
objSendMail.MailFormat = CdoMailFormatType
objSendMail.Importance = CdoNormal
objSendMail.Send
Set objSendMail = Nothing
' **********************************************************************************
Function MailBody()
Dim oConn
Dim oCmd
Dim oRs
Dim tmpBody
set oConn = CreateObject("ADODB.Connection")
oConn.Open("DATABASE=Northwind;DSN=Northwind;UID=sa;Password=;")
set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandText = "select * from Northwind.dbo.[10k_order_qry] order by subtotal desc"
oCmd.CommandType = 1
oCmd.Prepared = True
set oRs = oCmd.Execute
oRs.moveFirst
tmpBody = "<H2><FONT COLOR=Red>10K Customer Report</FONT></H2>"
tmpBody = tmpBody & "<B><FONT COLOR=Blue>As of " & Date() & "</FONT><B><BR><BR>"
tmpBody = tmpBody & "<TABLE BORDER=2><TR BGCOLOR=Skyblue ALIGN=Middle>"
tmpBody = tmpBody & "<TH><B>ORDER ID</B></TH>"
tmpBody = tmpBody & "<TH><B>SUBTOTAL</B></TH>"
tmpBody = tmpBody & "<TH><B>COMPANY</B></TH>"
tmpBody = tmpBody & "<TH><B>CONTACT</B></TH>"
tmpBody = tmpBody & "<TH><B>COUNTRY</B></TH>"
tmpBody = tmpBody & "<TH><B>PHONE</B></TH>"
while not oRs.EOF
tmpBody = tmpBody & "<TR BGCOLOR=Cornsilk><TD>" & oRs.Fields("OrderID") & "</TD>"
tmpBody = tmpBody & "<TD>" & "$" & oRs.Fields("Subtotal") & "</TD>"
tmpBody = tmpBody & "<TD>" & oRs.Fields("CompanyName") & "</TD>"
tmpBody = tmpBody & "<TD>" & oRs.Fields("ContactName") & "</TD>"
tmpBody = tmpBody & "<TD>" & oRs.Fields("Country") & "</TD>"
tmpBody = tmpBody & "<TD>" & oRs.Fields("Phone") & "</TD>"
oRs.moveNext
wend
tmpBody = tmpBody & "</TR></TABLE>"
MailBody = tmpBody
set oRs = nothing
set oCmd = nothing
set oConn = nothing
End Function
I tried to insert enough comments to clarify the script, but for those who have not worked with HyperText markup language (HTML), here is a brief legend of the tags that I used in building the body of the E-mail (report) message:
TAG
MEANING
<H2>…</H2>
Header Tag Size 2
<FONT COLOR=Red>…</FONT>
Font Color "Red"
<B>…</B>
Bold Text
<TABLE>…</TABLE>
Table Format
<TR>…</TR>
Table Row
<TH>…</TH>
Header Cell
<TD>…</TD>
Data Cell
Now, let’s run our Visual Basic script in the Windows Scripting Host. In Windows Explorer, right-click on MyScript.vbs. Select Properties; click the Script tab.
Figure 1
Check the "Stop script after specified number of seconds:" box, and allow enough time for the script to accomplish its goal (the default is 10 seconds). Press the OK button, and a new file is created with the same name as our script file, MyScript, but with the WSH extension – MyScript.WSH. The content of the WSH file resembles the old "ini" files:
Assuming, there are no bugs, after double-clicking MyScript.WSH, the mail recipient will receive an E-mail with the report as the message body, as follows:
10K Customer Report
As of
9/13/99
ORDER
ID
SUBTOTAL
COMPANY
CONTACT
COUNTRY
PHONE
10865
$16387.50
QUICK-Stop
Hors
t Kloss
Germany
0372-035188
10889
$11380.00
Rattlesnake Canyon
Grocery
Paula Wilson
USA
(505) 555-5939
10897
$10835.24
Hungry Owl All-Night
Grocers
Patricia McKenna
Ireland
2967
542
Now, that we are sure this script works fine, the next and final step is to schedule it for a daily, automatic delivery. For this piece of puzzle, I am going to use the Windows Command Scheduler that is a part of Resource Kit 4.0. You will find this utility under the Configuration module of this resource kit.
When you start the Command Scheduler, go to the Edit menu, and select Add. (See Fig. 2.)
Type the script name that you wish to process in the Command edit box. Since our project indicates that the Northwind Manager would like to receive this E-mail on a daily basis, Monday through Friday, select the Every radio button. Also, select the days of the week from the Days list box. I decided to process this E-mail at 5:00 AM.
Figure 2
Once you press the OK button, this command will be scheduled according to your settings, and an entry will be displayed as in Fig. 3.
Figure 3
The technologies Windows Scripting Host, CDONTS, and Command Scheduler covered here have extensive capabilities, and I tried to present a practical introduction.
About the Author
Shahriar Moosavizadeh, MCSD, is the Web development manager for Idax.com LLC, a subsidiary of Idax, Inc.. (http://www.idax.com) in Norfolk, Virginia. Shahriar is also involved in the development of eCommerce, application services hosting, and client/server projects. He is interested in your comments and suggestions for future articles, and can be contacted at shahriar@idax.com.
Free SMTP component that supports multiple file attachments, unlimited recipients, CC's, BCC's and REPLY-TO's. Sends messages as plain text or in the HTML format. Premium features include message queuing and deferred processing for high mail volumes. When used with AspEncrypt, generates S/MIME-enabled secure mail.
AspMail supports multiple file attachments (MIME and UUE), US ASCII and ISO-8859-1 character sets, 8bit subject lines, custom message content headers, custom message headers, MS Exchange priority headers, PGP and more.
DevMailer adds SMTP email sending abilities to ASP or Perl programs. Features include: attachments, failsafe queueing, redundant servers, standard message file support, and advanced activity logging. Also verify email addresses and send multiple messages on a single connection.
JangoMail, located at JangoMail.com, is a web-based service that sends mass
e-mails by connecting to data from your SQL Server or ODBC compliant
database. Unlike traditional ASP e-mail components, the JangoMail service
can also handle unsubscribes and bounces automatically and synchronize these
with your original web database. The only setup that is required is the
placement of one ASP file on your web server. Other features include
message open tracking and click tracking.
Send Email directly from you web page via your webserver. jMail will not start up any annoying email clients, just smoothly send the mail via the mailserver. Implement it with easy ASP code.
Mail for .NET is the first product for the NetToolworks.NET framework. Together they provide methods that send, receive, compose, edit, encode and decode e-mail messages. SMTP, POP, complex MIME messages, HTML messages, and file/memory streaming are also supported.
A single component that is limited in scope to five methods. The OCXMail ASP component allows you to send mail using the standard SMTP protocol from any program that can use ActiveX/OLE components.
The ocxQmail ASP component allows you to send mail using the standard SMTP protocol from any program that can use ActiveX/OLE components. ocxQmail queues up messages for batch delivery by a companion NT Service at intervals you specify in the Administration Windows GUI.
Your ASP pages do not have to wait for the mail message to be physically sent before continuing.
RobustPOP3 component allows you to retrieve mail using POP3 protocol. Features include: Retrieve Messages
Multiple File Attachments, File Attachments support MIME and UUEncode.
A full-featured SMTP e-mail client component that allows developers to send e-mail from any client. This award-winning control offers significantly better performance than other popular SMTP components. SoftArtisans SMTPmail is written in high-performance C++ and supports all threading models, file attachments and multiple encoding schemes. New features in version 2.0 include login authentication and mass mail. The new version also supports PGP encryption.
Simple mail strictly conforms to the original SMTP standard. It does not support enhanced features like attachments, MIME or multiple character sets. However, it offers high performance, ease of use and a very competitive low price.
Chilkat WebMail is a POP3/SMTP client component packed with
advanced features including: full S/MIME capability, MHTML,
multipart/alternative, multipart/related, attachments, advanced AES encryption, charset conversion, Outlook integration, progress monitoring,
import EML, import/export XML, mail-merge, distribution lists, Chilkat Zip
integration, Outlook contacts and distribution lists, bad email address
detection/collection, SMTP Windows Integrated Authorization, smart cards,
SMTP/POP3 server diagnostics, full control over Cryptographic Service
Providers for S/MIME, auto-handle any charset for any language, and more.
Jonathan Zufi shows how to use the XMLHTTP object within JavaScript or VBScript to validate form-field information without having to submit a page and wait for the result. [Read This Article][Top]
Firing events on a Web server is an easy task. However most of the easy solutions require you to have your own dedicated IIS or SQL Server on the Internet to play with, a privilege not shared by many. In this article, Matthew Muller shows you how to get the same functionality in a shared hosting environment.
[Read This Article][Top]
Web galleries are an easy way to add interactivity and content to your Web site. However, how do you keep the Web galleries consistent with your site and how do you overcome the deficiencies of your Web gallery creation tool? John Sorensen explains a simple way to do both. [Read This Article][Top]
Using classes in ASP 3.0 we can create dynamic arrays of objects. Donnell DeLeon Smith's article also shows how we can implement a class of dynamic arrays of objects several layers deep, if required. [Read This Article][Top]
Even though SMS is now in high gear, developers remain slated with restrictive limits to carrier resources. Sending an SMS message via e-mail requires the acceptance of several hidden flaws. Joe Lauer shows how to avoid these complications by sending a wireless text-message through the use of ASP. [Read This Article][Top]
Add punch to your validation routines by adding regular expressions. Further prepare yourself for the coming ASP.NET regular expression validation control. This article shows you how to use regular expressions and provides sample patterns for different user inputs. [Read This Article][Top]
Thought displaying file ACLs on a Web
page in a browser was impossible without Active Directory installed? Think again.
Through a patchwork of technologies, Larry Schwartz proves otherwise. [Read This Article][Top]
Servers-side validations on the client side...isn't that an oxymoron? Maybe, but Pandurang Nayak shows us how to accomplish a type of remote scripting using a mix of Javascript and ASP. [Read This Article][Top]
A function that calls itself repeatedly, satisfying some condition is
called a Recursive Function. Using recursion, we split a complex problem into its single simplest case.
The recursive function only knows how to solve that simplest case. You'll see the difference
between solving a problem iteratively and recursively later.
[Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
