Etiket arşivi: mail

MS SQL de bir sorgunun sonucunu HTML olarak mail attırmak

Yayın tarihi
1 
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( 
SELECT A.MATERIAL as 'td','',B.STEXT as 'td',''
,CONVERT(INTEGER,SUM(A.QUANTITY)) AS 'td','',A.QUNIT as 'td',''
,CONVERT(DATE,A.DOCDATE) as 'td' 
FROM IASINVITEM A WITH (NOLOCK),IASVAROPTIONX B WITH (NOLOCK)
WHERE A.CLIENT='00' AND A.COMPANY='01'
AND A.INVDOCTYPE='IC' AND A.WAREHOUSE='170' AND A.STOCKPLACE='01'
AND A.ISCANCELED=0
AND (A.MATERIAL='L5402G'
OR A.MATERIAL LIKE 'LMS__G')
AND B.OPTIONKEY LIKE SUBSTRING(A.VOPTIONS,4,2)
AND B.ATTRIBUTEKEY='01'
AND B.VARIANTKEY='B'
AND A.CREATEDAT>GETDATE()-1
GROUP BY A.MATERIAL,B.STEXT,A.QUNIT ,A.DOCDATE
ORDER BY A.MATERIAL,B.STEXT,A.DOCDATE

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body>
<H3>Üretim</H3>
<table border = 1> 
<tr>
<th> Ürün </th> <th> Beden </th> <th> Miktar </th> 
<th> Birim </th> <th> Tarih </th> </tr>'     

SET @body = @body + @xml +'</table></body></html>'
 
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileFabrika',  
@body = @body,
@body_format ='HTML',
@recipients = 'fahridonmez@mail.com', 
@subject = 'Çorap Üretimi' ;