Send Mail of SQL 2005 by StoreProcedure

วิธีการคือ

1. ให้ทำการสร้าง Profiles ของอีเมลล์ที่ DatabaseMail บน SQL Server ก่อน

2. เขียน SQL ดังนี้

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email ทดสอบภาษาไทยนะครับ'

EXEC msdb.dbo.sp_send_dbmail
@recipients='prompratan.n@rockworth.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;

*** ตรวจสอบ Mail Profile

SELECT * FROM msdb.dbo.sysmail_profile

SELECT * FROM msdb.dbo.sysmail_account

*** ตรวจสอบ Log Mail

SELECT * FROM msdb.dbo.sysmail_event_log

Parameter :

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @query_result_no_padding = ] query_result_no_padding ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]