--在Job BES_Daily_FTP_filedownload 中使用 ALTER proc [dbo].[RSP_FN_UNAPPLIED_Mail_Reminder] as Declare @MailAddr varchar(max) DECLARE @xml NVARCHAR(MAX) DECLARE @body NVARCHAR(MAX) --Generate receive person list set @MailAddr = '' select @MailAddr=@MailAddr+s.User_Mail+';' from Mst_UserInfo s where User_IsValid=1 and substring(User_IsMail,1,1)='8' and User_Mail is not null and User_Mail!='' order by s.User_Mail --print @MailAddr --generate mail body SET @xml = CAST(( SELECT [Business_Unit] 'td','' ,isnull([Customer_Name],remitter_name) 'td','' ,[Receipt_No] 'td','' ,convert(varchar(10),[Receipt_Date],120) 'td','' ,convert(varchar(100),cast(Receipt_Unapplied_Amount as money),1) 'td','' --Commas every three digits ,convert(varchar(100),cast(Receipt_Amount as money),1) 'td','' ,[Currency_Name] 'td','' ,Bank_Name 'td' ,'' ,[Receipt_Remark] 'td' -- ,[Bank_Account] -- ,[Customer_JDE_No] -- ,[Receipt_Year] -- ,[Receipt_Period] -- ,[Remitter_Bank_Name] -- ,[Bank_Branch] -- ,[Bank_Account] -- ,[Bank_Charge_Amount] -- ,[Receipt_Type] -- ,[Receipt_GL_Date] -- ,[Receipt_Applied_Amount] -- ,[Receipt_Status] -- ,[Receipt_Status_No] -- ,[Batch_Name] -- ,[Batch_Status] -- ,[Customer_Oracle_no] -- ,[Action_Date] -- ,[Serial_No] -- ,[Receipt_Remark] -- ,[IsHistory] -- ,[Download_Date] FROM [BankReceipt] left join mst_bankinfo on [Remitter_Bank_Name] = bank_id Where receipt_status_no in (100,300,1000) and business_unit in ('pvg','pws','sgc') and Receipt_Status<>'Unidentified' order by Receipt_No desc FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body ='<html><H1>Unapplied Receipt Report</H1> <body><style type="text/css"> h1,body{font:10pt,"Arial"} h1{font:small-caps 14pt} table,td,th {border:1px solid #7F7F7F;border-collapse:collapse;} table{100%} th{background-color:#C0C0C0;font-weight:bold} </style> <table> <tr> <th>Entity</th> <th>Remitter Name</th> <th>Receipt No</th> <th style="82px">Receive Date</th> <th>Receipt Remain Amt</th> <th>Receipt Total Amt</th> <th>Currency</th> <th>Remitter Bank Name</th> <th>Receipt Remark</th> </tr>' SET @body = @body + @xml +'</table></body></html>' --send mail --EXEC msdb.dbo.sp_send_dbmail --@blind_copy_recipients = @MailAddr, --@body = @body, --@body_format ='HTML', --@subject ='Unapplied Receipt Report' exec msdb.dbo.CL_SendSingleMail ''--sendtousermailSysMail [Sys.Admin@emerson.com] ,''--cc ,@MailAddr--@MailAddr--bcc ,'SysMail'--sendername ,'Sys.Admin@emerson.com'--senderaddr ,'Unapplied Receipt Report'--mailsubject ,@body--mailcontent ,'normal' --importance low/normal/high