Figure 1: Creating a Mail account
Many web applications use components or scripting to send mail. SQL Server has mail functionality built in and can sometimes be the best solution. Many times you want an email to be automated as a task -- for example, sending a list of store orders once a day or a list of files that have been uploaded. Other times, you want an email to be sent when a specific thing occurs -- an order table is updated, or a document is printed. This article will show how SQL Mail can be set up and automated within SQL Server without involving your web application.
SQL Mail can be set up with an existing mail account that is set up as a profile in Windows Messaging. This can be an Exchange Server, an Outlook profile, or an Internet mail account. You can create the profile by going into your Mail properties (from the Control Panel, Inbox icon on the desktop, or from Outlook). If you don't have the Windows Messaging on your server, you may have to install it from your Windows installation disc.
Open up the Mail control panel and click the Add button to create your mail profile for the SQL Server account:
Figure 1: Creating a Mail account
This can be any mail account, but you should set up a dedicated mail account for your SQL Server, so that it has full access to it at all times. Next choose the type of mail account this is. In this case we'll use an Internet mail account:
Figure 2: Setting up an internet mail account
Just like when you set up your email accounts in Outlook or Outlook Express, the mail profile needs your account information to make the connection to the mail server:
Figure 3: Setting the account properties
Note: If you cannot get the mail account configured correctly, consult the documentation at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sql64/in_introduction64_7xo2.asp for more details.
To see if the SQL Mail configuration is working, open up the Enterprise Manager and expand your server. Open up the Support Services folder and right-click on the SQL Mail icon. In the properties window there should be a dropdown list with your profile in it:
Figure 4: Setting up the account in Support Services
You should be able to click the test button and get a successful connection. If for some reason the test fails, make sure that Outlook Express is not set up as the default mail client by going to your Internet Options in Control Panels. Click the Programs tab and make sure Outlook Express isn't set up as the default email application. If you need to change the settings, stop and restart the server and try to connect again.
After configuring and testing SQL Mail with the test button, you can try to send an actual email with SQL mail to see if it's working properly. The xp_sendmail extended stored procedure is one of the great built-in stored procedures of SQL Server. The full syntax for it can be looked up in Books Online, but you can call it like this from the Query Analyzer while in the Master database:
EXEC xp_sendmail 'admin@mysite.com',
@subject = 'Testing SQL Mail',
@message = 'Testing'
SQL Mail can be used to send a static email, or you can use it to send a dynamic email. Calling it from the Master database again, you can access your database by using the fully qualified name of the database. To send query results to an email address of all orders within the last 24 hours:
EXEC xp_sendmail @recipients = 'admin@mysite.com',
@subject = 'Orders placed today',
@query = 'SELECT * FROM MyDatabase.dbo.Orders WHERE
(DATEDIFF(hour, OrderDate, GETDATE()) < 24)'
To use xp_sendmail from within a stored procedure in your own database, you should use the fully-qualified name master.dbo.xp_sendmail, as in this stored procedure that we've placed in the Northwind database:
CREATE PROCEDURE dbo.spOrderPlaced
@orderID int
AS
DECLARE @buildQuery varchar(512)
DECLARE @buildSubject varchar(80)
SET @buildQuery =
'SELECT P.ProductName, OD.UnitPrice, OD.Quantity
FROM Northwind.dbo.orders O
INNER JOIN Northwind.dbo.[Order Details] OD
ON O.orderid = OD.orderid
INNER JOIN Northwind.dbo.Products P
ON OD.ProductID = P.ProductID
WHERE O.orderid = ' +
CONVERT(varchar(20),@orderID)
SET @buildSubject = 'Your order placed on ' + CONVERT(varchar(20),getdate())
EXEC master.dbo.xp_sendmail 'tommuck@basic-drumbeat.com',
@subject = @buildSubject,
@query = @buildQuery
The query simply chooses the product name, unit price, and quantity ordered from one particular order. A query like this could be triggered by an actual order by a user by passing the order id to the stored procedure. The email could be sent to the customer, the administrator, or both. In this case, it was called from the Query Analyzer once again by specifying an account number:
exec spOrderPlaced 10267
If the email has sent successfully, you will see a message like this in the Query Analyzer window:
SQL Mail session started.
Mail sent.
The email that SQL builds isn't pretty, however you can freely format your email within the stored procedure itself. All this requires is a little bit of string manipulation within your stored procedure:
SELECT 'Your order comes to $' +
ltrim(rtrim(Convert(varchar(20,total_price))))
+
char(10)
+
'Thank you for your order.'
as Total FROM myTable
SQL Mail can be automated in several different ways. One way is to send an email from a trigger as the result of a database insert or update. This was shown in the article Working with SQL Server Triggers. Another way is to use SQL Agent to set up a scheduled job to fire off each day at a certain time. Take a look at this stored procedure:
CREATE PROCEDURE dbo.spOrdersPlacedToday
AS
DECLARE @buildSubject varchar(80)
DECLARE @today varchar(20)
-- set up today's date as a string
SET @today = CONVERT(varchar(2),Month(getdate())) + '/' + CONVERT(varchar(2),Day(getdate())) +
'/' + CONVERT(varchar(4),Year(getdate()))
--build the subject of the email
SET @buildSubject = 'Orders placed on ' + @today
--send today's orders email to the sales manager
EXEC master.dbo.xp_sendmail 'tommuck@basic-drumbeat.com',
@subject = @buildSubject,
@query = 'SELECT O.OrderID,O.CustomerID,
TotalOrder = sum(OD.unitprice * OD.Quantity * OD.Discount) FROM Northwind.dbo.Orders O
INNER JOIN Northwind.dbo.[Order Details] OD
ON O.OrderID = OD.OrderID WHERE DateDiff(hh,orderdate,getdate()) < 24
GROUP BY o.orderid,o.customerid'
The query is fairly simple and just grabs the OrderID, CustomerID, and the sum of all of the items on each particular order detail (unitprice * quantity * discount). The DateDiff built-in SQL Server function will give us a difference in hours between the OrderDate and the current system date. If that date is less than 24 hours, the result will be returned in the statement.
Now for the automation: we want this stored procedure to execute each day at 6 PM to send the orders of the day to the sales manager. The first step is to make sure that SQL Agent is running on the SQL Server machine. SQL Server Agent is a separate service that can be started with SQL Server. If it's not started, you can start it from the SQL Server Service Manager, the Enterprise Manager, or from the Services control panel. With SQL Server Agent running, you can now schedule jobs to run automatically.
Like most other things within SQL Server, there is a wizard that you can use to create the job. The job scheduler can be accessed from the Tools menu in the Enterprise Manager:
Figure 5: Scheduling a job
This brings up the Create Job Wizard. The jobs that you can run can consist of any TransactSQL statements, batch files, shell system commands, or ActiveScript commands. Click the Next button. Since we are executing a simple stored procedure, we will choose Transact-SQL command and click Next:
Figure 6: Choosing a SQL command
The next screen allows you to choose the database that the SQL statement will execute on and gives you a box to enter your SQL. In this case, we are simply calling the stored procedure that we just created – spOrdersPlacedToday. You can click the Parse button to check your syntax, also:
Figure 7: Testing the SQL statement
Next, you are given the opportunity to create the schedule that you want followed for the job. This will be a daily recurring job, so you should choose the recurring option from the list of choices and click the Schedule button:
Figure 8: Scheduling the execution
A job can be scheduled to run on a recurring basis as a daily, weekly, monthly, hourly, or other timeframe – even once per minute. You have a great deal of flexibility, but this particular job will run once per day at 6:00 PM:
Figure 9: Editing the recurring time
You can also have the status of the job emailed to an administrator, but we'll leave that option off for this particular job. Since the job consists of an email being sent, there would be little benefit to sending an email telling you that the email was sent.
Figure 10: Setting up notifications
That's all there is to it. The last screen shows you the summary of the job you just set up, and allows you the chance to give the job a name that is relevant to the task at hand. After clicking the Finish button, you should see a success box:
FIgure 11: Successful execution
Now that the job is registered, it will show up under the list of jobs for your server under the SQL Agent. By double-clicking a job in the list, you can also access all of the information you just entered in the wizard – in fact, you may decide that you don't need the wizard if you understand the contents of the tabbed interface. As with all of SQL Server's wizards, they are there to assist you to do some of the common tasks, but you can perform the tasks manually too:
Figure 12: Viewing the properties of the recurring job
SQL Mail is fairly limited, though, and you may want to use another mail component, such as CDONTS, Jmail, or ASPMail. This is entirely possible within SQL Server as well, by using the sp_OACreate extended stored procedure in the Master database. The syntax is very similar to the ASP code that you would use to create the object within an ASP page. The difference here, though, is the fact that you can call it from your SQL database as the result of a transaction such as a database insert. Let's take a look at a simple stored procedure that uses the CDONTS object that is preinstalled with IIS. You can use the same principle with any mail component:
CREATE Procedure spSendCDOMail
@OrderID int, @ToAddress varchar(80),
@TotalPrice money
AS
SET nocount on
DECLARE @oMail int
--Object reference
DECLARE @resultcode int
DECLARE @subject varchar(80)
DECLARE @FromAddress varchar(100)
DECLARE @body varchar(8000)
SET @Subject = 'Your order was placed on ' + CONVERT(varchar(20),getdate())
SET @body = 'Your order number ' + CONVERT(varchar(6),@orderid) + ' of ' + char(10) + CONVERT(varchar(20),@TotalPrice) + ' was received on ' + CONVERT(varchar(20),getdate()) + char(10) + 'Thank you for your order!'
SET @FromAddress = 'admin@basic-ultradev.com'
SET @Subject = 'Your order placed on ' + CONVERT(varchar(20),getdate())
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
if @resultcode = 0
BEGIN EXEC @resultcode = sp_OASetProperty @oMail, 'From', @FromAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @ToAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END SET nocount off
The stored procedure can be called from the Query Analyzer, or from within a trigger or stored procedure. The calling syntax is:
exec spSendCDOMail 10267, 'tommuck@basic-drumbeat.com', 19.99
Here we are just passing hard-coded values for @OrderId, @ToAddress, and @TotalPrice. These could just as easily have been passed from an ASP, ColdFusion, PHP, or JSP page, or from within a trigger that was activated by a database insert.
You can see that the sp_OACreate extended stored procedure invokes the server object CDONTS much the way you would do it from a script in a Web application or from a Visual Basic program. This stored procedure could be triggered by an insert to the database by a customer ordering something from your Web site. The advantages are not immediately apparent, but there are pluses to using a robust mail component with full HTML and multipart capabilities, or a mail component with a built-in looping structure for delivering many messages.
One of the other advantages in using SQL Mail with triggers is that the web application is completely removed from the process. For example, I have one web application that allows users to create documents, print them, and have them sent via snail mail. The web application is written in ColdFusion, but the printing component is written in Visual Basic. The printing might occur three weeks after the order is placed or immediately upon order. A simple trigger on the Orders table sends an email from SQL Server when a document is printed by the VB program or by the web application. The email functionality did not have to be programmed in two completely different environments.
Also, emails can be generated as alerts of a database field reaching a certain level, such as to alert you of a product shortage. For example, if you are selling books and a specific title is almost out of stock, a trigger can generate an email when the stock in the Products table reaches a certain level. This would take extra steps if done in the web application.
Similarly, a Job database might alert users when a specific job matching a job seeker's criteria becomes available. A scheduled task might search the job database using a table of criteria, and send email to users that have matching jobs. This might tie into a web-based job search site, but would be difficult to accomplish using only web scripting.
This article wasn't intended as a comprehensive SQL Mail tutorial, but rather a simple introduction to give you an idea of the types of things that can be done with SQL Mail.