In the implementation system, from time to time, users put forward the hope that the system can automatically push mail. Because of the limited tools and capabilities at hand, many requirements are realized by using the mail trigger of sql server.
Steps:
1. Configure mailbox. Steps are simple, there are many posts on the Internet, manual direct in the management - Database Mail configuration can be. After the configuration is completed, you can right-click to test whether the mailbox is working properly.
2. Making a Send Mail Script
3. sql server agent defines periodic plan
Mail scripting:
Scenario 1: The business department hopes to provide a sample inventory once a week, that is, the results of sql queries will be sent to the designated personnel in the form of attachments.
1 EXEC msdb.dbo.sp_send_dbmail 2 @profile_name ='< account name >'- - defined sql server mailbox account name 3 @recipients ='< mail account >'-- Accounts that need to be sent for multiple purposes; intervals, it is recommended that an email group be used to manage addresses that need to be sent. 4@body = The stored procedure finished successfully.'--Mail body 5@subject='Sample Warehouse Bill of Materials'- - Mail Header 6 @execute_query_database='UFDATA_001_2016'-- Query database 7 8 -- Queries to be executed 9 10 @query = 'select 11 distinct substring (cinvcode, 4,100) material number 12 from 13 CurrentStock 14 where 15 cwhcode = 12 16 and iquantity >=1', 17 @attach_query_result_as_file = 1, 18 @query_attachment_filename = 'item.csv'
Result of mail delivery
Scenario 2, the user system triggers the email to the other party after the external users report to the customer for approval completed by the OA system. Because the format of external mail triggered automatically by OA system is obvious, it is said that it needs js to write code, because it is not familiar with it, so it is realized by using the mail function of sql server.
Write a view in advance, three fields, the mailbox to be sent, the subject of the mail, the content of the mail.
In the example, the subject and the main body are taken as one and implemented with circular statements.
1 declare @mail nvarchar(200); 2 declare @note nvarchar(500); 3 4 declare c cursor -- cursor 5 For select email, note from cux_dls_notice_v where operation date +'+ operation time >= DATEADD (MINUTE, -60, GETDATE ()) - Send the record of the last hour, and schedule the task to be executed once in 60 minutes. 6 7 open c 8 fetch next from c into @mail,@note; 9 10 while @@FETCH_STATUS = 0 11 12 begin 13 14 EXEC msdb.dbo.sp_send_dbmail 15 @profile_name='< account name >'- - defined sql server mailbox account name 16@recipients=@mail,--mailboxes to be sent 17@subject=@note,--mail title 18@body=@note -- Mail topic 19 fetch next from c into @mail,@note; 20 end 21 close c; 22 deallocate c;
Scenario 3, or in the OA system, the sales application is submitted for approval after a special offer. The examiner system can receive an email notification and return to the system for approval after discussion with the sales in the email. Due to the large content of the application form, it is necessary to use the html sending format.
Similar to scenario 2, the emphasis is on the need to generate html formats for mail topics.
Or make a view of what needs to be displayed. I personally like to make a view, so I can adjust the view if there is any change.
/*Declare variables*/ declare @tableHTML varchar(max) declare @mail nvarchar(200); declare @note nvarchar(500); --Setting greetings set @tableHTML = '<html><body><table><tr><td><p><font color="#000080" size="3" face="Verdana">Hello!</font></p><p style="margin-left:30px;"><font size="3" face="Verdana">Please approve the following price application:</font></p></td></tr>'; --Setting table header set @tableHTML=@tableHTML +'<tr><td><table border="1" style="border:1px solid #d5d5d5;border-collapse:collapse;border-spacing:0;margin-left:30px;margin-top:20px;"><tr style="height:25px;background-color: rgb(219, 240, 251);"> <th style="width:100px;">RFQ No</th> <th style="width:200px;">sales</th> <th style="width:60px;">PL3</th> <th style="width:80px;">Customer</th> <th style="width:100px;">disty_name</th> <th style="width:60px;">2nd disty</th> <th style="width:80px;">Sold To Customer</th> <th style="width:80px;">Part No</th> <th style="width:100px;">Currency</th> <th style="width:60px;">Volume</th> <th style="width:100px;">Requested DC</th> <th style="width:100px;">Customer RP</th> <th style="width:100px;">Competitor</th> <th style="width:100px;">Competitor PN</th> <th style="width:80px;">Competitor Price</th></tr>'; --Enable cursors declare c cursor for --Query results select a.email ,a.note ,@tableHTML+'<tr><td align="center">'+rfq_quotation_number+'</td>' +'<td align="center">'+lastname+'</td>' +'<td align="center">'+pl3+'</td>' +'<td align="center">'+customer+'</td>' +'<td align="center">'+disty_name+'</td>' +'<td align="center">'+snd_disty+'</td>' +'<td align="center">'+sold_to_customer+'</td>' +'<td align="center">'+fully_part_no+'</td>' +'<td align="center">'+currency+'</td>' +'<td align="center">'+volume+'</td>' +'<td align="center">'+requested_disty_cost+'</td>' +'<td align="center">'+cust_requested_price+'</td>' +'<td align="center">'+competitor+'</td>' +'<td align="center">'+competitor_part_no+'</td>' +'<td align="center">'+Competitor_Price+'</td></tr>' 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(100)),'') competitor ,isnull(cast(competitor_part_no as varchar(50)),'') competitor_part_no ,isnull(cast(competitor_price as varchar(10)),'') competitor_price from cux_rfq_v where currentnodetype = 1 and lastoperatedate + ' '+ lastoperatetime >= DATEADD(MINUTE,-60,GETDATE()) --Find the record of the latest 60 points and send it ) a open c fetch next from c into @mail ,@note ,@tableHTML; while @@FETCH_STATUS = 0 begin EXEC msdb.dbo.sp_send_dbmail @profile_name= '<Account Name>', --Defined sql server Name of mailbox account ,@recipients=@mail ,@subject=@note ,@body= @tableHTML ,@body_format='HTML' fetch next from c into @mail ,@note ,@tableHTML; end close c; deallocate c;
