December 6, 2012

Database Mail Views

Se more view version maps here: Microsoft Sql System View Version Maps

sysmail_allitems

Contains one row for each message processed by Database Mail. Use this view when you want to see the status of all messages. To see only messages with the failed status, use sysmail_faileditems (Transact-SQL)1. To see only unsent messages, use sysmail_unsentitems (Transact-SQL)2. To see only messages that were sent, use sysmail_sentitems (Transact-SQL)3.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
mailitem_id int Identifier of the mail item in the mail queue.
profile_id int The identifier of the profile used to send the message.
recipients varchar(max) The e-mail addresses of the message recipients.
copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message.
blind_copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message but whose names do not appear in the message header.
subject nvarchar(510) The subject line of the message.
body varchar(max) The body of the message.
body_format varchar(20) The body format of the message. The possible values are TEXT and HTML.
importance varchar(6) The importance parameter of the message.
sensitivity varchar(12) The sensitivity parameter of the message.
file_attachments varchar(max) A semicolon-delimited list of file names attached to the e-mail message.
attachment_encoding varchar(20) The type of mail attachment.
query varchar(max) The query executed by the mail program.
execute_query_database sysname The database context within which the mail program executed the query.
attach_query_result_as_file bit When the value is 0, the query results were included in the body of the e-mail message, after the contents of the body. When the value is 1, the results were returned as an attachment.
query_result_header bit When the value is 1, query results contained column headers. When the value is 0, query results did not include column headers.
query_result_width int The query_result_width parameter of the message.
query_result_separator char(1) The character used to separate columns in the query output.
exclude_query_output bit The exclude_query_output parameter of the message. For more information, see sp_send_dbmail (Transact-SQL)4.
append_query_error bit The append_query_error parameter of the message. 0 indicates that Database Mail should not send the e-mail message if there is an error in the query.
send_request_date datetime The date and time the message was placed on the mail queue.
send_request_user sysname The user who submitted the message. This is the user context of the database mail procedure, not the From: field of the message.
sent_account_id int The identifier of the Database Mail account used to send the message.
sent_status varchar(8) The status of the mail. Possible values are:
sent - The mail was sent.

unsent - Database mail is still attempting to send the message.

retrying - Database Mail failed to send the message but is attempting to send it again.

failed - Database mail was unable to send the message.
sent_date datetime The date and time that the message was sent.
last_mod_date datetime The date and time of the last modification of the row.
last_mod_user sysname The user who last modified the row.

TSQL

Sql 2005
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_allitems
Sql 2008
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_allitems
Sql 2008 R2
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_allitems
Sql 2012
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_allitems

Back to Top


sysmail_event_log

Contains one row for each Windows or SQL Server message returned by the Database Mail system. (Message in this context refers to a message such as an error message, not an e-mail message.) Configure the Logging Level parameter by using the Configure System Parameters dialog box of the Database Mail Configuration Wizard, or the sysmail_configure_sp1 stored procedure, to determine which messages are returned.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
log_id int Identifier of items in the log.
event_type varchar(11) The type of notice inserted in the log. Possible values are errors, warnings, informational messages, success messages, and additional internal messages.
log_date datetime The date and time the log entry is made.
description nvarchar(max) The text of the message being recorded.
process_id int The process id of the Database Mail external program. This typically changes each time the Database Mail external program starts.
mailitem_id int Identifier of the mail item in the mail queue. NULL if the message is not related to a specific e-mail item.
account_id int The account_id of the account related to the event. NULL if the message is not related to a specific account.
last_mod_date datetime The date and time of the last modification of the row.
last_mod_user sysname The user who last modified the row. For e-mails, this is the user who sent the mail. For messages generated by the Database Mail external program, this is the user context of the program.

TSQL

Sql 2005
SELECT [log_id], [event_type], [log_date], [description], [process_id], [mailitem_id], [account_id], [last_mod_date], [last_mod_user] FROM sysmail_event_log
Sql 2008
SELECT [log_id], [event_type], [log_date], [description], [process_id], [mailitem_id], [account_id], [last_mod_date], [last_mod_user] FROM sysmail_event_log
Sql 2008 R2
SELECT [log_id], [event_type], [log_date], [description], [process_id], [mailitem_id], [account_id], [last_mod_date], [last_mod_user] FROM sysmail_event_log
Sql 2012
SELECT [log_id], [event_type], [log_date], [description], [process_id], [mailitem_id], [account_id], [last_mod_date], [last_mod_user] FROM sysmail_event_log

Back to Top


sysmail_faileditems

Contains one row for each Database Mail message with the failed status. Use this view to determine which messages were not successfully sent. To see all messages processed by Database Mail, use sysmail_allitems (Transact-SQL)1. To see only unsent messages, use sysmail_unsentitems (Transact-SQL)2. To see only messages that were sent, use sysmail_sentitems (Transact-SQL)3. To view e-mail attachments, use sysmail_mailattachments (Transact-SQL)4.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
mailitem_id int Identifier of the mail item in the mail queue.
profile_id int The identifier of the profile used to submit the message.
recipients varchar(max) The e-mail addresses of the message recipients.
copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message.
blind_copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message but whose names do not appear in the message header.
subject nvarchar(510) The subject line of the message.
body varchar(max) The body of the message.
body_format varchar(20) The body format of the message. The possible values are TEXT and HTML.
importance varchar(6) The importance parameter of the message.
sensitivity varchar(12) The sensitivity parameter of the message.
file_attachments varchar(max) A semicolon-delimited list of file names attached to the e-mail message.
attachment_encoding varchar(20) The type of mail attachment.
auery     varchar(max) The query executed by the mail program.
execute_query_database sysname The database context within which the mail program executed the query.
attach_query_result_as_file bit When the value is 0, the query results were included in the body of the e-mail message, after the contents of the body. When the value is 1, the results were returned as an attachment.
query_result_header bit When the value is 1, query results contained column headers. When the value is 0, query results did not include column headers.
query_result_width int The query_result_width parameter of the message.
query_result_separator char(1) The character used to separate columns in the query output.
exclude_query_output bit The exclude_query_output parameter of the message. For more information, see sp_send_dbmail (Transact-SQL)5.
append_query_error bit The append_query_error parameter of the message. 0 indicates that Database Mail should not send the e-mail message if there is an error in the query.
send_request_date datetime The date and time the message was placed on the mail queue.
send_request_user sysname The user who submitted the message. This is the user context of the database mail procedure, not the From: field of the message.
sent_account_id int The identifier of the Database Mail account used to send the message. Always NULL for this view.
sent_status varchar(8) The status of the mail. Always failed for this view.
sent_date datetime The date and time that the message was removed from the mail queue.
last_mod_date datetime The date and time of the last modification of the row.
last_mod_user sysname The user who last modified the row.
query     varchar(max) The query executed by the mail program.

TSQL

Sql 2005
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [auery], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_faileditems
Sql 2008
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [auery], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_faileditems
Sql 2008 R2
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_faileditems
Sql 2012
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_faileditems

Back to Top


sysmail_mailattachments

Contains one row for each attachment submitted to Database Mail. Use this view when you want information about Database Mail attachments. To review all e-mails processed by Database Mail use sysmail_allitems (Transact-SQL)1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
attachment_id int Identifier of the attachment.
mailitem_id int Identifier of the mail item that contained the attachment.
filename nvarchar(520) The file name of the attachment. When attach_query_result is 1 and query_attachment_filename is NULL, Database Mail creates an arbitrary filename.
filesize int The size of the attachment in bytes.
attachment varbinary(max) The content of the attachment.
last_mod_date datetime The date and time of the last modification of the row.
last_mod_user sysname The user who last modified the row.

TSQL

Sql 2005
SELECT [attachment_id], [mailitem_id], [filename], [filesize], [attachment], [last_mod_date], [last_mod_user] FROM sysmail_mailattachments
Sql 2008
SELECT [attachment_id], [mailitem_id], [filename], [filesize], [attachment], [last_mod_date], [last_mod_user] FROM sysmail_mailattachments
Sql 2008 R2
SELECT [attachment_id], [mailitem_id], [filename], [filesize], [attachment], [last_mod_date], [last_mod_user] FROM sysmail_mailattachments
Sql 2012
SELECT [attachment_id], [mailitem_id], [filename], [filesize], [attachment], [last_mod_date], [last_mod_user] FROM sysmail_mailattachments

Back to Top


sysmail_sentitems

Contains one row for each message sent by Database Mail. Use sysmail_sentitems when you want to see which messages were successfully sent. To see all messages processed by Database Mail, use sysmail_allitems (Transact-SQL)1. To see only messages with the failed status, use sysmail_faileditems (Transact-SQL)2. To see only unsent or retrying messages, use sysmail_unsentitems (Transact-SQL)3. To see e-mail attachments, use sysmail_mailattachments (Transact-SQL)4.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
mailitem_id int Identifier of the mail item in the mail queue.
profile_id int The identifier of the profile used to send the message.
recipients varchar(max) The e-mail addresses of the message recipients.
copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message.
blind_copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message but whose names do not appear in the message header.
subject nvarchar(510) The subject line of the message.
body varchar(max) The body of the message.
body_format varchar(20) The body format of the message. The possible values are TEXT and HTML.
importance varchar(6) The importance parameter of the message.
sensitivity varchar(12) The sensitivity parameter of the message.
file_attachments varchar(max) A semicolon-delimited list of file names attached to the e-mail message.
attachment_encoding varchar(20) The type of mail attachment.
query varchar(max) The query executed by the mail program.
execute_query_database sysname The database context within which the mail program executed the query.
attach_query_result_as_file bit When the value is 0, the query results were included in the body of the e-mail message, after the contents of the body. When the value is 1, the results were returned as an attachment.
query_result_header bit When the value is 1, query results contained column headers. When the value is 0, query results did not include column headers.
query_result_width int The query_result_width parameter of the message.
query_result_separator char(1) The character used to separate columns in the query output.
exclude_query_output bit The exclude_query_output parameter of the message. For more information, see sp_send_dbmail (Transact-SQL)5.
append_query_error bit The append_query_error parameter of the message. 0 indicates that Database Mail should not send the e-mail message if there is an error in the query.
send_request_date datetime The date and time the message was placed on the mail queue.
send_request_user sysname The user who sent the message. This is the user context of the database mail procedure, not the From: field of the message.
sent_account_id int The identifier of the Database Mail account used to send the message.
sent_status varchar(8) The status of the mail. Always sent for this view.
sent_date datetime The date and time that the message was sent.
last_mod_date datetime The date and time of the last modification of the row.
last_mod_user sysname The user who last modified the row.

TSQL

Sql 2005
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_sentitems
Sql 2008
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_sentitems
Sql 2008 R2
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_sentitems
Sql 2012
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_sentitems

Back to Top


sysmail_unsentitems

Contains one row for each Database Mail message with the unsent or retrying status. Messages with unsent or retrying status are still in the mail queue and may be sent at any time. Messages can have the unsent status for the following reasons: The message is new, and though the message has been placed on the mail queue, Database Mail is working on other messages and has not yet reached this message. The Database Mail external program is not running and no mail is being sent. Messages can have the retrying status for the following reasons: Database Mail has attempted to send the mail, but the SMTP mail server could not be contacted. Database Mail will continue to attempt to send the message using other Database Mail accounts assigned to the profile that sent the message. If no accounts can send the mail, Database Mail will wait for the length of time configured for the Account Retry Delay parameter and then attempt to send the message again. Database Mail uses the Account Retry Attempts parameter to determine how many times to attempt to send the message. Messages retain retrying status as long as Database Mail is attempting to send the message. Use this view when you want to see how many messages are waiting to be sent and how long they have been in the mail queue. Normally the number of unsent messages will be low. Conduct a benchmark test during normal operations to determine a reasonable number of messages in the message queue for your operations. To see all messages processed by Database Mail, use sysmail_allitems (Transact-SQL)1. To see only messages with the failed status, use sysmail_faileditems (Transact-SQL)2. To see only messages that were sent, use sysmail_sentitems (Transact-SQL)3.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
mailitem_id int Identifier of the mail item in the mail queue.
profile_id int The identifier of the profile used to submit the message.
recipients varchar(max) The e-mail addresses of the message recipients.
copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message.
blind_copy_recipients varchar(max) The e-mail addresses of those who receive copies of the message but whose names do not appear in the message header.
subject nvarchar(510) The subject line of the message.
body varchar(max) The body of the message.
body_format varchar(20) The body format of the message. The possible values are TEXT and HTML.
importance varchar(6) The importance parameter of the message.
sensitivity varchar(12) The sensitivity parameter of the message.
file_attachments varchar(max) A semicolon-delimited list of file names attached to the e-mail message.
attachment_encoding varchar(20) The type of mail attachment.
query varchar(max) The query executed by the mail program.
execute_query_database sysname The database context within which the mail program executed the query.
attach_query_result_as_file bit When the value is 0, the query results were included in the body of the e-mail message, after the contents of the body. When the value is 1, the results were returned as an attachment.
query_result_header bit When the value is 1, query results contained column headers. When the value is 0, query results did not include column headers.
query_result_width int The query_result_width parameter of the message.
query_result_separator char(1) The character used to separate columns in the query output.
exclude_query_output bit The exclude_query_output parameter of the message. For more information, see sp_send_dbmail (Transact-SQL)4.
append_query_error bit The append_query_error parameter of the message. 0 indicates that Database Mail should not send the e-mail message if there is an error in the query.
send_request_date datetime The date and time the message was placed on the mail queue.
send_request_user sysname The user who submitted the message. This is the user context of the database mail procedure, not the From field of the message.
sent_account_id int The identifier of the Database Mail account used to send the message. Always NULL for this view.
sent_status varchar(8) Will be unsent if Database Mail has not attempted to send the mail. Will be retrying if Database Mail failed to send the message but is trying again.
sent_date datetime The date and time the Database Mail last attempted to send the mail. NULL if Database Mail has not attempted to send the message.
last_mod_date datetime The date and time of the last modification of the row.
last_mod_user sysname The user who last modified the row.

TSQL

Sql 2005
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_unsentitems
Sql 2008
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_unsentitems
Sql 2008 R2
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_unsentitems
Sql 2012
SELECT [mailitem_id], [profile_id], [recipients], [copy_recipients], [blind_copy_recipients], [subject], [body], [body_format], [importance], [sensitivity], [file_attachments], [attachment_encoding], [query], [execute_query_database], [attach_query_result_as_file], [query_result_header], [query_result_width], [query_result_separator], [exclude_query_output], [append_query_error], [send_request_date], [send_request_user], [sent_account_id], [sent_status], [sent_date], [last_mod_date], [last_mod_user] FROM sysmail_unsentitems

Back to Top

No comments:

Post a Comment

Total Pageviews