How to use Database Mail for sending emails
Applies to:
- Microsoft SQL Server 2005
Sending a test email
- EXEC msdb.dbo.sp_send_dbmail
- @profile_name = 'Main',
- @body = 'Artsi Fartsi...',
- @subject = 'Blah Blah',
- @recipients = 'junk@sqlhacks.com'
- go
Mail queued.
- The job is done by the Microsoft stored procedure: sp_send_dbmail.
- These 4 items are the minimum for sending a successful email. In today's world , most mail servers require both a subject and body before accepting the email.
- The profile on line 2 tells the DatabaseMail90.exe which mail server and account to use.
Email sent verification
- SELECT CAST(subject AS VARCHAR(20)) AS 'Subject',
- CAST(body AS VARCHAR(20)) AS 'Body',
- send_request_date,
- CAST(send_request_user AS VARCHAR(20)) AS 'Sent by',
- sent_account_id,
- sent_status,
- sent_date
- FROM msdb.dbo.sysmail_allitems;
- go
Subject Body send_request_date Sent by sent_account_id sent_status sent_date -------------------- -------------------- ----------------------- -------------------- --------------- ----------- ----------------------- Blah Blah Artsi Fartsi... 2007-09-17 08:38:52.187 SQLSERVER\froggy NULL failed 2007-09-17 08:43:37.280 Blah Blah Artsi Fartsi... 2007-09-17 08:44:46.873 SQLSERVER\froggy 6 sent 2007-09-17 08:44:47.000 (2 row(s) affected)
- sysmail_allitems keeps the history of the emails sent, including successes and failures.
- I use the cast to shrink the columns to make the listing more readable.
- The SQL request are made under: send_request_date.
- The email is sent to the mail server under the sent_date.
- This does not mean that the mail was successfully received by the recipient, just that it was successfully passed to the mail server.
Emailing a query result
You can use a query in the sp_send_dbmail, then the result of that query can be sent either as part of the body or as an attachment.
- EXEC msdb.dbo.sp_send_dbmail
- @profile_name = 'Main',
- @body = 'Here it is...',
- @subject = 'SQL query',
- @recipients = 'junk@sqlhacks.com',
- @execute_query_database = 'sql911',
- @attach_query_result_as_file = 0,
- @query = 'select sales_date,sales_qty,sales_price from sales;'
- go
Mail queued.
- SELECT CAST(subject AS VARCHAR(17)) AS 'Subject',
- send_request_date,
- CAST(send_request_user AS VARCHAR(20)) AS 'Sent by',
- CAST(query AS VARCHAR(55)) AS 'Query',
- sent_account_id,
- sent_status,
- sent_date
- FROM msdb.dbo.sysmail_allitems;
- go
Subject send_request_date Sent by Query sent_account_id sent_status sent_date ----------------- ----------------------- -------------------- ------------------------------------------------------- --------------- ----------- ----------------------- Blah Blah 2007-09-17 08:38:52.187 SQLSERVER\froggy NULL NULL failed 2007-09-17 08:43:37.280 Blah Blah 2007-09-17 08:44:46.873 SQLSERVER\froggy NULL 6 sent 2007-09-17 08:44:47.000 SQL query 2007-09-17 08:54:53.247 SQLSERVER\froggy select sales_date,sales_qty,sales_price from sales; 6 sent 2007-09-17 08:54:53.000 (3 row(s) affected)
Here is the email as received:
from: sql <system@sqlhacks.com> to: junk@sqlhacks.com subject: SQL query date: 17 Sep 2007 08:54:53 -0700 Here it is... sales_date sales_qty sales_price ----------------------- ----------- -------------- 2005-10-06 10:01:01.000 10.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2005-11-06 10:01:01.000 9.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2005-12-06 11:01:01.000 8.00 11.75 2006-02-06 13:01:01.000 NULL NULL 2006-03-06 14:01:01.000 5.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2006-04-06 15:01:01.000 4.00 11.75 2006-05-06 16:01:01.000 3.00 11.75 2004-10-06 10:01:01.000 NULL NULL 2005-11-06 10:01:01.000 9.00 11.75 2006-06-06 17:01:01.000 2.00 11.75 2006-01-06 12:01:01.000 7.00 11.75 2006-02-06 13:01:01.000 6.00 11.75 2005-03-06 14:01:01.000 5.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2006-10-06 10:01:01.000 3.00 11.75 2007-06-06 17:01:01.000 2.00 11.75 2007-04-06 00:00:00.000 4.00 11.75 2007-05-01 00:00:00.000 5.00 11.75 2007-07-06 00:00:00.000 1.00 11.75 2007-05-03 09:00:00.000 3.00 11.75 2007-05-05 04:00:00.000 1.00 11.75 2006-10-06 10:01:01.000 10.00 11.75 2004-03-06 14:01:01.000 NULL NULL 2005-04-06 15:01:01.000 4.00 11.75 2005-05-06 16:01:01.000 3.00 11.75 2006-06-06 17:01:01.000 2.00 11.75 2006-07-06 19:01:01.000 1.00 11.75 2006-03-06 14:01:01.000 5.00 11.75 2006-04-06 15:01:01.000 4.00 11.75 2007-05-07 11:00:00.000 2.00 11.75 2006-06-06 17:01:01.000 NULL NULL 2006-07-06 19:01:01.000 1.00 11.75 2005-10-06 10:01:01.000 10.00 11.75 2007-05-08 15:00:00.000 1.50 11.75 2005-12-06 11:01:01.000 8.00 11.75 2006-01-06 12:01:01.000 7.00 11.75 2007-05-07 11:30:00.000 2.00 10.50 2007-05-08 13:30:00.000 1.00 10.00 2006-07-06 19:01:01.000 1.00 11.75 2007-05-09 14:00:00.000 1.25 10.00 2007-11-06 10:01:01.000 9.00 11.75 2007-05-10 14:00:00.000 5.00 10.00 2007-05-11 14:15:00.000 4.00 10.00 2007-05-12 15:00:00.000 2.00 10.00 2007-05-13 16:00:00.000 1.00 10.00 2007-05-08 05:00:00.000 1.00 10.00 2007-05-07 00:00:00.000 2.00 10.00 2007-05-14 11:00:00.000 3.00 10.00 2006-07-06 00:00:00.000 1.00 11.75 2007-05-11 13:00:00.000 3.50 10.00 2007-05-08 05:00:00.000 1.00 10.00 2007-05-08 07:00:00.000 2.00 10.00 2007-05-08 11:00:00.000 3.00 10.00 (57 rows affected)
- You need to select the database as on line 6: @execute_query_database = 'sql911' .
- You assign the value to @query = 'select sales_date,sales_qty,sales_price from sales;' as on line 8 as a interactive query. The query can also be built under T-SQL.
- With @attach_query_result_as_file = 0, you decide if you want the query to be in the body of the email after the parameter @body.
- If you set @attach_query_result_as_file = 1, then the result of the query will be placed in an attachment, instead of inside the body.
- The order of the parameters is not important since you are using the reserved words for the stored procedure: sp_send_dbmail.