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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.