use AdventureWorks
go
---
--enviar correo con informacion atraves de archivo adjunto
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EmailOperador',
@recipients = 'cristiansanchez_99@hotmail.com',
@query='Select * from AdventureWorks.Person.Contact
where ContactID<=500',
@subject = 'Listado de Contactos de la BD AdventureWorks',
@attach_query_result_as_file=1;
---
--enviar correo con condiciones
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EmailOperador',
@recipients = 'kevinjvn@yahoo.com',
@query=' select ContactID, Title, FirstName, LastName, EmailAddress
From AdventureWorks.Person.Contact
where EmailPromotion=1 and Title="Mr."',
@subject = 'Contar Productos',
@attach_query_result_as_file=1;
select ContactID, Title, FirstName, LastName, EmailAddress
From AdventureWorks.Person.Contact
where EmailPromotion=1 and Title='Mr.'
---
--codigo HTML
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Trabajando con Producto Vendedor</H1>' +
N'<table border="1">' +
N'<tr><th>IdProducto</th><th>IdVendedor ID</th>' +
N'<th>Precio</th><th>Precio Venta</th><th>Minima Orden</th>' +
N'<th>Maxima orden</th></tr>' +
CAST (( SELECT td = ProductID,'',
td = VendorID,'',
td = StandardPrice,'',
td = LastReceiptDate,'',
td = MinOrderQty,'',
td = MaxOrderQty,''
FROM AdventureWorks.Purchasing.ProductVendor
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) + N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EmailOperador',
@recipients='kevinjvn@yahoo.com',
@subject = 'Catalogo de Productos',
@body = @tableHTML,
@body_format = 'HTML' ;
|