Shulou(Shulou.com)06/01 Report--

SQLServer email warning

Question:

When the OA system pushes to the heterogeneous system, the push fails occasionally for various reasons. In order to avoid the problem escalation, the administrator needs to know the push failure immediately and intervene manually.

Problem analysis:

The status of the to-do push is recorded in the wx_scanlog table of the OA database. A resultstatus status of-1 indicates that the to-do push failed.

Solution:

One: SQLServer trigger

In order to know the to-do push failure message in time, you can use the SQLServer trigger to verify the resultstatus status value when new data is added to the wx_scanlog table, and automatically send mail to the specified mailbox when the value is-1.

Two: Grafana to check the to-do status

Connect to the OA database through Grafana, and specify a time interval to view the to-do status, such as the following SQL:

Select top 10 scantime, content, resultcontent from wx_scanlog where resultstatus =-1 order by scantime desc

One: SQLServer trigger

Https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms189635(v=sql.90)

The process is as follows:

-- the following starts to configure sql to send email:

-- enable sql server mail

Exec sp_configure 'show advanced options', 1

Go

Reconfigure

Go

-- configuration option 'show advanced options' has been changed from 0 to 1. Please run the RECONFIGURE statement to install.

Exec sp_configure 'Database Mail XPs', 1

Go

Reconfigure

Go

-- configuration option 'Database Mail XPs' has been changed from 0 to 1. Please run the RECONFIGURE statement to install.

-- use the following statement to check whether the database mail feature is enabled successfully and the database configuration information:

-- query configuration information of the database

Select * from sys. Configurations

-- check whether database mail is enabled. A value value of 1 means it is enabled, and 0 means it is not enabled.

Select name, value, description

Is_dynamic, is_advanced

From sys. Configurations

Where name like'% mail%'

-name value description is_dynamic is_advanced

-Database Mail XPs 1 Enable or disable Database Mail XPs 1 1

-step 2:

If exists (SELECT * FROM msdb.. Sysmail_account WHERE NAME = 'chenmail')

Begin

EXEC msdb.. Sysmail_delete_account_sp @ account_name = 'chenmail'

End

Exec msdb.. Sysmail_add_account_sp-create an email account

@ account_name = 'cjcamail'-- Mail account name

, @ email_address = 'chenjuchao163@163.com'-the sender's email address

, @ display_name = 'chenjuchao'-name of the sender

, @ replyto_address = null-- reply address

, @ description = null-- email account description

, @ mailserver_name = 'smtp.163.com'-- Mail server address

, @ mailserver_type = 'SMTP'-mail protocol

, @ port = 25-- Mail server port

, @ username = 'chenjuchao163'-- user name

, @ password ='*'- password

, @ use_default_credentials = 0-- whether to use default credentials. 0 is No, 1 is Yes.

, @ enable_ssl = 1-whether ssl encryption is enabled. 0 is No, 1 is Yes.

, @ account_id = null-- output parameter, which returns the ID of the created mail account

-step 3:

If exists (SELECT * FROM msdb.. Sysmail_profile where NAME = NumberSendEmailProfile0323')-determines whether a mail profile named SendEmailProfile0323 exists

Begin

Exec msdb.. Sysmail_delete_profile_sp @ profile_name = 'SendEmailProfile0323'-- Delete the mail profile named SendEmailProfile0323

End

Exec msdb.. Sysmail_add_profile_sp-add Mail Profil

@ profile_name = 'SendEmailProfile0323',-- profile name

@ description = 'database send mail profile',-- profile description

@ profile_id = NULL-- output parameter that returns the ID of the created mail profile

-step 4:

-- Mail account is associated with mail profile

Exec msdb.. Sysmail_add_profileaccount_sp

@ profile_name = 'SendEmailProfile0323',-- Mail profile name

@ account_name = 'chenmail',-- email account name

@ sequence_number = 1-the order of account in profile. A profile can have multiple different mail accounts.

-- at this point, the configuration for sql to send mail is basically over. Below, create a trigger to send an email to the user after the user has successfully registered.

Then create an after trigger of type insert:

Create trigger undo_fail_cjc_tr

-- alter trigger undo_fail_cjc_tr

On wx_scanlog

After insert

As

Declare @ errormsg nvarchar 1000

Declare @ resultcontent nvarchar

Declare @ content nvarchar

Declare @ title nvarchar

Declare @ xxx nvarchar 1000

Declare @ count int

Declare @ id int

Select @ count = COUNT (1) from inserted

Select @ id = id from inserted

Select @ resultcontent = (select CAST (resultcontent as nvarchar (1000)) from wx_scanlog where id = @ id)

Select @ content = (select CAST (content as nvarchar (1000)) from wx_scanlog where id = @ id)

The title of set @ xxx = 'content is as follows:' + @ content + CHAR (13) + 'resultcontent:' + @ resultcontent

-- select @ msgcode=msgcode,@errormsg=errormsg from inserted

-- if (@ count > 0)

If ((@ count > 0) and (select resultstatus from inserted) ='- 1')

Begin

Set @ title ='OA (official system) failed to push Eanar, please deal with it in time!'

Exec msdb. Dbo. Sp_send_dbmail @ profile_name = 'SendEmailProfile0323',-- Mail profile name

@ recipients = 'cjc@xxx.com',-- email address

@ subject ='OA (official system) failed to push Eanar, please deal with it in time!'--email title

@ body = @ xxx,-- message content

@ body_format = 'text'-- the type of message content. Text is text and can also be set to html.

End

Go

About two or three seconds after the above statement is executed, you will receive an email (if there is no error). If you do not receive an email, you can use the following statement to check the delivery of the email.

Use msdb

Go

Select * from sysmail_allitems-the status of email delivery, which can be used to check whether the email is sent successfully

Select * from sysmail_mailitems-record of sending mail

Select * from sysmail_event_log-Database mail log, which can be used to query whether an error has been reported.

Email:

Two: Grafana to check the to-do status

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.