How to use SQL database to send mail
This article mainly introduces how to use SQL database to send mail, the article is very detailed, has a certain reference value, interested friends must read it!
First, start the mail service first
SQL SCRIPTS
Exec master..sp_configure 'show advanced options',1
Go
Reconfigure
Go
Exec master..sp_configure 'Database mail XPs',1
Go
Reconfigure
Go
2. Create the account needed to send email
Exec msdb..sysmail_add_account_sp
@ account_name = 'OPSDBA',-- email account (account) name
@ description = 'Mail account for administrative emurmail.'
@ email_address = 'monitor.dataops@mail.COM',-- sender email address
@ display_name = 'dataops_monitor',-- Sender display name
@ mailserver_name = 'smtp mail server address',-- smtp mail server address
@ port = 25
Official sample
Https://msdn.microsoft.com/zh-cn/library/ms182804.aspx
Verification
EXECUTE msdb.dbo.sysmail_help_account_sp
Third, create a profile
-- create a profile
Exec msdb..sysmail_add_profile_sp
@ profile_name = 'monitor',--profile name
@ description=' Monitoring Mail profile'
Verification
EXECUTE msdb.dbo.sysmail_help_profile_sp
4. Associate account and profile
Exec msdb..sysmail_add_profileaccount_sp
@ profile_name = 'monitor',--profile name
@ account_name = 'OPSDBA',--account name
@ sequence_number = 1--account in order in profile
Verification
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@ principal_name = 'dba_monitor'
@ profile_name = 'monitor'
@ is_default = 1
5. Send mail
Exec msdb..sp_send_dbmail
@ profile_name = 'monitor',--profile name
@ recipients = 'rr@123.com',-- recipient mailbox (multiple mailboxes are separated by semicolons)
@ subject = 'SQL Server 2005 Mail Test',-- message title
@ body = 'Hello Mail! test'-- message content
-= =
Declare @ sql varchar 8000
Set @ sql='SELECT top 20 a [databse _ name]
, a.[table _ name]
, a.[row _ count]
, a.[reserved _ space]
, a.[used _ space] [used_space_0408]
, b.[used _ space] [used_space_0401]
, a.[index _ space]
, a.[unused _ space]
, a.[record _ time]
, convert (int,replace (A. [used _ space],'+''+ 'KB'+''''+','+''''+''''+'))-convert (int,replace (b. [used _ space],' +''+ 'KB'+''''+','+''''+''''+')) inc_space_KB
FROM [DBCenter]. [dbo]. [viewTableSpace] a join [DBCenter]. [dbo]. [viewTableSpace] b
On a.databse_name=b.databse_name and a.table_name=b.table_name and convert (varchar (10), a. [record _ time], 120) = convert (varchar (10), dateadd (day,0,getdate ()), 120)
And convert (varchar (10), b. [record _ time], 120) = convert (varchar (10), dateadd (day,-7,getdate ()), 120)
Order by inc_space_KB desc'
-- print @ sql
EXEC msdb.dbo.sp_send_dbmail
@ profile_name = 'monitor'
@ recipients = 'yuqing.zhu@datayes.com'
@ query = @ sql
@ subject = 'weekly table increase top 20'
@ attach_query_result_as_file = 1
The above is all the contents of the article "how to send mail using SQL database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
