Shulou(Shulou.com)05/31 Report--

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!

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.