Database mail

After configuring Database Mail in SQL Server, you can use the stored procedure msdb.dbo.sp_send_dbmail to send mail to a specific User, or notify the DBA database of an exception in the first place, which is a very good feature.

First, configure database mail

1. In the Management of SSMS, click Database Mail to open Database Mail Configuration Wizard

2, create Profile

When sending database mail, you need to specify a Profile,Profile configuration document for database mail, including SMTP Server address, SMTP Server authentication, and so on.

Click Add to create a SMTP Account.

3. To add SMTP Account, you need to configure the sending address of Database mail, SMTP Server URL and SMTP Server Authentication

4, set the default Profile

If the default Profile is set, @ profile_name is not required to explicitly specify Profile name when executing msdb.dbo.sp_send_dbmail.

5. Send test mail

Database Mail has its own "Send Test E-Mail" and "View Database Mail Log" functions, which are used to check the sending status of Database mail.

Second, use TSQL statement to send mail

1. Use msdb.dbo.sp_send_dbmail to send mail, because default profile is set, so you don't need to display and use @ Profile_Name to specify Profile.

EXEC msdb.dbo.sp_send_dbmail @ recipients='xxx@domainname.com', @ subject='Test email subject', @ body='test email body'--, @ profile_name='default'

You can also send mail in HTML format using msdb.dbo.sp_send_dbmail, which needs to be specified in the parameter @ body_format.

[@ body_format=] 'body_format'

The default value is Text, which means that the body of the message is sent in text format; in HTML format, you can use HTML tags

2. Send mail in text format

EXEC msdb.dbo.sp_send_dbmail @ recipients='xxx@domainname.com', @ subject='Test email subject', @ body='test email body'--, @ profile_name='default_ProfileName'--, @ body_format='text'

The default value of the @ Body_Format parameter is Text

3. Send mail in Html format

Declare @ htmlbody varchar (max) set @ htmlbody=' first row second row'EXEC msdb.dbo.sp_send_dbmail @ recipients='xxx@domainname.com', @ subject='Test email subject', @ body=@htmlbody--, @ profile_name='default_ProfileName', @ body_format='Html'

4. Use the query result as the body of mail

The stored procedure msdb.dbo.sp_send_dbmail can execute a select query clause and send the query result as an attachment.

EXEC msdb.dbo.sp_send_dbmail @ recipients='xxx@domainname.com', @ subject='Test email subject'--, @ profile_name='default_ProfileName'--, @ body_format='text', @ query='select * from db_study.dbo.test', @ attach_query_result_as_file = 1