Enviar resultados de Query en SQL Server vía Mail.
El Problema: Resulta que necesitaba hacer un gráfico de tendencia en Grafana, sin embargo el plugin de tendencia se me hacia rudimentario y la solución para implementarlo vía SQL Server que se parece a algo así (asumiendo que se tiene un ID secuencial):
create table #temp ( entity_id int, value int, [date] datetime ) insert into #temp (entity_id, value, [date]) values (1,10,'20140102 07:00:00 AM'), (1,20,'20140102 07:15:00 AM'), (1,30,'20140102 07:30:00 AM'), (2,50,'20140102 07:00:00 AM'), (2,20,'20140102 07:47:00 AM'), (3,40,'20140102 07:00:00 AM'), (3,40,'20140102 07:52:00 AM') select entity_id, 1.0*sum((x-xbar)*(y-ybar))/sum((x-xbar)*(x-xbar)) as Beta from ( select entity_id, avg(value) over(partition by entity_id) as ybar, value as y, avg(datediff(second,'20140102 07:00:00 AM',[date])) over(partition by entity_id) as xbar, datediff(second,'20140102 07:00:00 AM',[date]) as x from #temp where [date] >= '20140102 07:00:00 AM' and [date] < '20140102 08:00:00 AM' ) as Calcs group by entity_id having 1.0*sum((x-xbar)*(y-ybar))/sum((x-xbar)*(x-xbar)) > 0
La neta se me hacía muy complejo, así que pensé, ¿por que no enviar el resultado de la consulta a una hora determinada a mi correo, después aplicar tendencia con excel y finalmente insertar el resultado de excel con un bulk insert, y pues funcionó.
Para contextualizar, primero la estructura de datos en la tabla es muy simple son tres columnas Fecha, Total, FactoryID; algo así:
Fecha|Total|Planta
2018-09-28|7237.14|FACTORY1
2018-09-29|5509.88|FACTORY1
2018-09-30|4903.39|FACTORY1
2018-10-01|4498.54|FACTORY1
2018-10-02|2139.11|FACTORY1
2018-10-03|8998.51|FACTORY1
2018-10-04|5423.95|FACTORY1
2018-10-05|5354.85|FACTORY1
2018-10-06|5311.04|FACTORY1
2018-10-07|5267.23|FACTORY1
Entonces, avanzando por partes, primero necesitaba una query en SQL que me trajera sólo los últimos 7 días de esa tabla ya que tiene el acumulado de todo el año, esta es:
SET NOCOUNT ON select * from Totales where Fecha > CONVERT(date, GETDATE() - 8) order by Planta, Fecha
Bien, primer parte resuelta, observamos que tiene activado SET NOCOUNT esto es importante para que no envíe el resultado de la consulta con el total de registros, ¿y ahora como demonios envíamos el resultado de esta consulta vía mail?, bien la respuesta que yo encontré (aclaro, puede haber un método mejor y más simplificado,…)
Primero una consulta (que se deja como procedimiento almacenado a ejecutarse a una hora específica), esta consulta usa dos herramientas, xp_cmdshell y sqlcmd, ahi denle una leída a la documentación para que vean como usarlos y que pueden realizar con ellos, en este caso grosso modo el primero lo uso para ejecutar al segundo y algunos comandos adicionales, la cosa queda así:
--Se selecciona la base a usar use TUBASE Go --Se declaran las variables necesarias DECLARE @Ruta varchar(20) DECLARE @Nombre VARCHAR(20) DECLARE @ComandoA VARCHAR(40) DECLARE @ComandoB VARCHAR(120) DECLARE @ComandoC VARCHAR(120) --Se asignan valores a las variables, en este caso para los comandos se usaron estas variables para poder: -- 1- Eliminar todo los archivos txt de la ruta de almacenaje (para no crear garbage) -- 2- Asignar la cadena de comando de sqlcmd concatenando la fecha como nombre del archivo -- 3- ejecutar un script de powershell pasando como argumento el nombre del archivo generado en el paso 2 set @Ruta = 'TURUTA' set @Nombre=CONVERT(varchar,getdate(),105)+'.txt' set @ComandoA ='del /F /S /Q C:\TURUTA\*.txt' set @ComandoB ='sqlcmd -i C:\TURUTA\Last7days.sql -S localhost -d TUBASE -E -s ";" -o '+@Ruta+@Nombre set @ComandoC = 'powershell -file C:\TURUTA\attachtrend.ps1 '+ @Ruta + @Nombre -- Se ejecutan los comandos, uno a la vez, easy tiger. EXEC xp_cmdshell @comandoA EXEC xp_cmdshell @ComandoB EXEC xp_cmdshell @ComandoC
Como les comentaba con esta consulta se crea un procedimiento almacenado se programa a una hora y voilà.
La cereza del pastel, es el script de powershell que hace las veces de enviar el archivo TXT generado por SQLCMD, una rutina simple que toma como parametro el nombre del archivo generado:
param([string]$nombre)
$smtpClient = new-object system.net.mail.smtpClient
$smtpClient.Host = "TUSERVERSMTP"
$smtpClient.Port = TUPUERTO
$emailfrom = "EMAILORIGEN"
$emailto = "EMAILDESTINO"
$subject = "ASUNTODEMAIL"
$body = "TEXTODEMAIL"
$emailMessage = New-Object System.Net.Mail.MailMessage
$emailMessage.From = $EmailFrom
$emailMessage.To.Add($EmailTo)
$emailMessage.Subject = $Subject
$emailMessage.Body = $Body
$emailMessage.Attachments.Add($nombre)
$SMTPClient.Send($emailMessage)
Con todo esto como magia, me llega un txt del diario 7:30 AM, el cual modifico en excel para agregar las tendencias por los siguientes 3 días, luego lo exporto como TXT sin header (con nombre ‘trend.txt’) y lo inserto en mi tabla con un BULK insert, así:
BULK INSERT Totales
FROM 'C:\TURUTA\trend.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
);
Finalmente el resultado en grafana es este:
Espero le sirva a alguien, Salu2, Emmanuel.
