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

This article mainly introduces the SQL database msdb.dbo.sp_send_dbmail function to send mail example analysis, the article is very detailed, has a certain reference value, interested friends must read it!

In the implementation system, from time to time, some users hope that the system can push mail automatically. Due to the limited tools and capabilities on hand, many requirements are realized with the help of sql server mail trigger.

Steps:

1. Configure mailbox. The steps are brief, there are a lot of posts on the Internet, manual direct in the management-database mail configuration can be. After the configuration is complete, you can right-click to test whether the mailbox is working properly.

2. Make a script to send mail.

3. Sql server agent defines cycle plan

Mail scripting:

Scenario 1: the business department hopes to provide sample inventory once a week, that is, the results of the sql query will be sent to the designated personnel in the form of an attachment.

EXEC msdb.dbo.sp_send_dbmail @ profile_name ='',-- the defined sql server email account name @ recipients ='',-- the account that needs to send mail, using multiple Interval It is recommended to use a mail group to manage the address to be sent @ body = 'The stored procedure finished successfully.',-- email body @ subject =' sample warehouse bill of materials', email header @ execute_query_database = 'UFDATA_001_2016',-- database of the query-- query to be executed @ query =' select distinct substring (cinvcode). 4100) material number from CurrentStock where cwhcode = 12 and iquantity > = 1cm, @ attach_query_result_as_file = 1, @ query_attachment_filename = 'item.csv'

The result of sending an email

In scenario 2, the user system triggers an email to the other party after the external user reports to the customer for approval in the OA system. Because the OA system automatically triggers the external mail format to be displayed, it is said that js is required to write code, because it is not familiar with it, so it is realized with the help of the mail function of sql server.

Write a view in advance, three fields, the mailbox you need to send, the subject of the email, and the content of the email.

In the example, the topic and the body are taken as one, which is implemented by a circular statement.

Declare @ mail nvarchar; declare @ note nvarchar; declare c cursor-- cursor for select email,note from cux_dls_notice_v where operatedate +''+ operatetime > = DATEADD (MINUTE,-60,GETDATE ())-take the record of the last hour and send it, and the scheduled task is executed every 60 minutes. Open c fetch next from c into @ mail,@note; while @ @ FETCH_STATUS = 0 begin EXEC msdb.dbo.sp_send_dbmail @ profile_name=',-- defined sql server mailbox account name @ recipients=@mail,-- mailbox to be sent @ subject=@note,-- email title @ body=@note-- email subject fetch next from c into @ mail,@note; end close c; deallocate c

Scenario 3, still in the OA system, the sales application is submitted for approval after the special price, and the approver system can receive an email notification, and then return to the system for approval after email and sales discussion. As the application form contains a lot of content, it needs to be sent in html format.

The approach is similar to scenario 2, where the focus is that the subject of the message needs to be generated in html format.

Or to show the content into a view, I personally like to do view, so that there are any changes to adjust the view.

/ * declare the variable * / declare @ tableHTML varchar (max) declare @ mail nvarchar; declare @ note nvarchar;-- set the greeting set @ tableHTML ='

Hello!

Please approve the following price application:

';-- set the header set @ tableHTML=@tableHTML+'RFQ NosalesPL3Customerdisty_name2nd distySold To CustomerPart NoCurrencyVolumeRequested DCCustomer RPCompetitorCompetitor PNCompetitor Price' -- enable cursor declare c cursor for-- query result selecta.email,a.note @ tableHTML+''+rfq_quotation_number+''+''+lastname+''+''+pl3+''+''+customer+''+''+disty_name+''+''+snd_disty+''+''+sold_to_customer+''+''+fully_part_no+''+''+currency+''+''+volume+''+''+requested_disty_cost+''+''+cust_requested_price+''+''+competitor+''+''+competitor_part_no+''+''+Competitor_Price+''from (select email,note Rfq_quotation_number, lastname,pl3, customer Chinese +'/'+ customer English as customer,disty_name,snd_disty,sold_to_customer,fully_part_no,currency,isnull (cast (volume as nvarchar (10)),') volume,isnull (cast (requested_disty_cost as varchar (10)),'') requested_disty_cost,isnull (cast (cust_requested_price as varchar (10)),'') as cust_requested_price,isnull (cast (competitor as varchar (10)) '') competitor,isnull (cast (competitor_part_no as varchar (50)),'') competitor_part_no,isnull (cast (competitor_price as varchar (10)),'') competitor_pricefrom cux_rfq_v where currentnodetype = 1 and lastoperatedate +'+ lastoperatetime > = DATEADD (MINUTE,-60,GETDATE ())-find the record of the last 60 points And send) aopen cfetch next from c into @ mail,@note,@tableHTML While @ @ FETCH_STATUS = 0beginEXEC msdb.dbo.sp_send_dbmail@profile_name=',-- the defined sql server email account name, @ recipients=@mail,@subject=@note,@body= @ tableHTML,@body_format='HTML'fetch next from c into @ mail,@note,@tableHTML;endclose

The above is all the contents of the article "sample Analysis of emails sent by SQL Database msdb.dbo.sp_send_dbmail functions". 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.