yrcdba7
asked on
sending and receiving emails from SQL server?
Experts,
How can I set up sending and receiving email in SQL server mail? How does it work? how can I understand the processes?
Lynn
How can I set up sending and receiving email in SQL server mail? How does it work? how can I understand the processes?
Lynn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please see code from my solution for sending HTML email at:
https://www.experts-exchange.com/questions/26826472/sp-send-dbmail-format.html?sfQueryTermInfo=1+10+30+dbmail+send+sp
To send query output as attachemnt:
eclare @report_file_name varchar(30),
@sql varchar(max),
@emailbody varchar(4000)
set @sql = N'SET NOCOUNT ON
SELECT whatever FROM whatever_table WHERE whatever'
SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdat e(), 112) + '.txt'
SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd): '+ convert(varchar(10),getdat e(), 102)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile', -- mail profile here
@recipients='user@mail.com ',
@subject = 'This is a TEST email',
@body = @emailbody,
@body_format = 'TEXT',
@query = @sql,
@attach_query_result_as_fi le = 1,
@execute_query_database = 'YourDB', -- your database name here
@query_attachment_filename = @report_file_name,
@query_result_header = 1,
@query_result_no_padding = 1,
@query_result_separator = ' ',
@query_result_width = 1000;
To send attachmet only:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile', -- mail profile here
@recipients='user@mail.com ',
@subject = 'Your image file has arrived...',
@body = 'Here is your image file - see attached',
@body_format = 'TEXT',
@file_attachments = 'E:\PicturesFolder\Pic1.jp g';
https://www.experts-exchange.com/questions/26826472/sp-send-dbmail-format.html?sfQueryTermInfo=1+10+30+dbmail+send+sp
To send query output as attachemnt:
eclare @report_file_name varchar(30),
@sql varchar(max),
@emailbody varchar(4000)
set @sql = N'SET NOCOUNT ON
SELECT whatever FROM whatever_table WHERE whatever'
SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdat
SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd): '+ convert(varchar(10),getdat
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile', -- mail profile here
@recipients='user@mail.com
@subject = 'This is a TEST email',
@body = @emailbody,
@body_format = 'TEXT',
@query = @sql,
@attach_query_result_as_fi
@execute_query_database = 'YourDB', -- your database name here
@query_attachment_filename
@query_result_header = 1,
@query_result_no_padding = 1,
@query_result_separator = ' ',
@query_result_width = 1000;
To send attachmet only:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile', -- mail profile here
@recipients='user@mail.com
@subject = 'Your image file has arrived...',
@body = 'Here is your image file - see attached',
@body_format = 'TEXT',
@file_attachments = 'E:\PicturesFolder\Pic1.jp
ASKER
Thank you!
This article walks you though configuring it and sending messages, as well as following up to ensure they were delivered:
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/