SQL Server databases use stored procedures and dbmail to implement timed messages _mssql

I've talked about how to configure Database Mail forwarding in a database (note: The Database Mail feature is based on SMTP and first configures SMTP features in the system.) That is, in the Add/Remove Programs panel, Add/Remove Windows components, select and double-click to open IIS or applications, check the SMTP SERVICE, and then click Next. Generally do not need this step, direct configuration can be

This article gives a usage example, combining stored procedure and job to realize timed sending mail from the database:
1. Create stored procedures, call the sp_send_dbmail stored procedure in the new stored procedure (note: \ ' In code is only the escape character automatically added when code is added, only single quotation mark in the real code)

Copy Code code as follows:

Create PROCEDURE [dbo]. [Sp_send_error_alert]
As
Declare
@v_contentnvarchar (max),
@v_mail_tonvarchar (500),
@v_bodynvarchar (max),
@v_titilenvarchar (100)
, @v_br_idnvarchar (50)
, @v_installmentsnvarchar (10)
, @v_remarknvarchar (100)
BEGIN
Setnocounton;
Set@v_titile=\ ' test email content title;
Set@v_content=\ ' \ ';

Declarecursor_repaymentcursorfor
SELECT
br_id
, installments
, remark
Fromdw_account_repaymentt
where
Status!=1
Andis_del=0
Orderbyt.add_datetimedesc;

Opencursor_repayment
Fetchnextfromcursor_repaymentinto
@v_br_id--Label ID
, @v_installments--Number of periods
, @v_remark--Error description
while@ @FETCH_STATUS =0
BEGIN

set@v_content= @v_content +\ ' <tr><td>\ ' + @v_br_id +\ ' </td>\ ' +\ ' <td>\ ' + @v_installments +\ ' </td>\ ' +\ ' <td>\ ' + @v_remark +\ ' </td></tr>\ ';

Fetchnextfromcursor_repaymentinto
@v_br_id--Label ID
, @v_installments--Number of periods
, @v_remark--Error description
End

closecursor_repayment;--Close Cursor
deallocatecursor_repayment;--Empty cursor

Iflen (@v_content) >0
Begin
Set@v_body=\ ' set@v_body= @v_body + @v_content +\ ' </table></body>EXECmsdb.dbo.sp_send_dbmail
@recipients =n\ ' Receive user address 1@qq.com, receive user address 2@qq.com\ ', @body = @v_body, @body_format =\ ' html\ '
, @subject = @v_titile, @profile_name =\ ' last node's profile name (Db_profiler) \

Create a job (SQL Server Agent-> job), set execution parameters, and execute this stored procedure at timed intervals to achieve the effect of timed mail delivery.