Generar informes en Excel desde SQL Server

Excel reconoce las etiquetas HTML. Así pues, para generar informes en este formato basta sólo darle formato a la salida de nuestra consulta. Puede usarse directamente desde QA o bien crear un 'Job' que genere un archivo *.xls y luego automáticamente lo envíe por correo.

Dejo un ejemplo de un script que genera un informe sobre incidencias ocurridas durante la semana de guardia. Los comentarios están en inglés ( idioma oficial de mi empresa)

El script se invoca desde un 'job' que luego envía el informe por correo usando xp_smtp_sendmail

/*

File: oncall.sql
Desc: Script to generate a xls report of the oncall records
Author: Luis González
@@bof_revsion_marker
revision history

yyyy/mm/dd by description
========== ======= ========================================================
2006/10/11 luis v1.0.0.0 created
@@eof_revsion_marker
***************************************************************************
*/
/*
Instructions to run this script on query analizer:

Copy and paste this script on Query Analyzer
On the Menu, go to TOOLS-OPTIONS and on the RESULTS tab, on the MAXIMUM CHARACTER PER COLUMN set the value to 1000
On Menu, go to QUERY and click RESULTS ON FILE
Run the query
*/

use DBMONITOR
GO
SET NOCOUNT ON

print '<BR>'
print '<P ALIGN ="left" ><B>DBCOE ON CALL LOG REPORT</B></P>'
print '<P ALIGN ="left"><I> from ' + convert(char(20),getdate()-8) +'to '+ convert(char(20),getdate())+'</I></P>'
print '<BR>'

IF (SELECT COUNT(*) FROM oncalllog WHERE datediff(day,dateoccurred,getdate())<8 )= 0 /*checks only the last seven days*/


select '<DIV ALIGN="center"><TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="7" ALIGN="center" VALIGN="top"><A><B>THERE ARE NOT ONCALL RECORDS</B></A> </TD></TR>'

ELSE

BEGIN
select '<DIV ALIGN="center"><TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="7" ALIGN="center" VALIGN="top"><A><B>REPORT </B></A> </TD></TR>'
union all
select ' <TR BGCOLOR="EEEEEE">
<TD ALIGN= "left" WIDTH= "5%"><B>Dba Name</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Date Occurred</B></TD>
<TD ALIGN= "left" WIDTH= "5%"><B>PQR #</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Server Name</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Instance Name DB</B></TD>
<TD ALIGN= "left" WIDTH= "29%"><B>Problem Description</B></TD>
<TD ALIGN= "left" WIDTH= "40%"><B>Resolution Description</B></TD>
</TR>'
union all



select '<TD VALIGN="top"> '+ rtrim(dbaname)+ ' </TD>
<TD VALIGN="top"> '+ convert(char(10),dateoccurred,121)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(pqr#)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(servername)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(instancename)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(problemdesc)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(resolutiondesc)+ ' </TD>
</TR>' from oncalllog where datediff(day,dateoccurred,getdate())<8 -- the last seven days

union all


SELECT '</TABLE>'

print '<BR>'
print '<P ALIGN ="left" ><B>END OF REPORT</B></P>'
END



SET NOCOUNT OFF