One of the things missing in SSMS is the ability to make “pretty” reports. Most user do not care for the plain text reports that are directly copied from the query window. In fact most of the “higher-ups” demand a report with more pizzazz. So how can we accommodate a “pretty” report, on a frequent schedule? One solution is to use SSRS, but we will be exploring a dynamic solution that uses dynamic sql and sp_send_dbmail. I will be using sp_send_dbmail() because it gives me the ability to put the results of a query into the body of email, or an attachment. How does a process that “automagically” creates and emails a pretty HTML report, with dynamic filtering and conditional formatting sound? I bet you are on the edge of your seat…. well maybe not :), but I bet you are at least thinking about how much time this could save you. So how do we do this? I started by breaking the logic down into three procedures. Two of these procedures are used to actually create and email the report and the third stored procedure is used to strip invalid characters out of the dynamic sql.
You can download all the code here: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/SQL%20HTML%20Report/sp%7C_Create%7C_HTML%7C_Rpt.zip. Lets start with the first stored procedure. The first procedure strips invalid characters from a given input string. This stored procedure helps protect our data by reducing the risk of SQL injection attack. You can customize the regular expression to include characters you deem valid. These checks are done against the filtering and conditional formatting inputs. I chose to exclude all and only include valid characters versus choosing to include all and excluding specific characters. This is a more secure model and is equally configurable. The stored procedure accepts one parameter @str, which is an input string.
Parameters:
- @str – Input string to be stripped for invalid characters
USE master
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RemoveChars]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION dbo.sp_RemoveChars
END
GO
CREATE FUNCTION dbo.sp_RemoveChars(@Str varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
declare @NewStr varchar(500),
@i int
set @i = 1
set @NewStr = ''
while @i <= len(@str)
begin
--grab digits or ( in regex) decimal
if substring(@str,@i,1) LIKE '[0-9A-Z%\/.<>!=]' OR substring(@str,@i,1) = ' '
begin
set @NewStr = @NewStr + substring(@str,@i,1)
end
else
begin
set @NewStr = @NewStr
end
set @i = @i + 1
end
RETURN Rtrim(Ltrim(@NewStr))
END
GO
The next stored procedure we will be creating is sp_Create_HTML_Rpt, which is the main report. This stored procedure actually generates the HTML. You should point this code at an table or view. There are lots of parameters here and I will go through each of them. Essentially this code gives you the flexibility to filter the data, create custom conditional formatting etc..
Parameters:
- @Schema – The schema of the object
- @object – The object being reported on. This can be a view or a table.
- @ColList – A comma delimited list of columns that you want to report on. The default is all columns for a given table/view.
- @FilterCol – The column that should filter the data
- @FilterOperator – The operand used to filter the data
- @FilterCriteria – The filter expression
- @HeaderBGColor – The color of the background for the header row.
- @Align – The alignment of the column data. The default is left.
- @ConditionColumn – This is the column that is used to conditionally apply formatting to a row.
- @ConditionOperator – This is the operand of the conditional expression
- @ConditionBGColor – The color of the background for the conditional expression, if met.
- @FontSize – The size of the body font.
--====================================================================================
-- CHANGE DB CONTEXT TO MASTER
--====================================================================================
USE master
GO
--====================================================================================
-- DROP THE SP IF IT ALREADY EXISTS
--====================================================================================
IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'sp_Create_HTML_Rpt')
BEGIN
DROP PROCEDURE sp_Create_HTML_Rpt;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_Create_HTML_Rpt
@schema varchar(100) = 'dbo',
@object varchar(100),
@ColList varchar(max) = 'ALL',
@FilterCol varchar(100) = NULL,
@FilterOperator varchar(6) = '=',
@FilterCriteria varchar(50) = NULL,
@HeaderBGColor VARCHAR(7) = '#dcdcdc',
@Align Varchar(10) = 'LEFT',
@ConditionColumn varchar(100) = NULL,
@ConditionOperator varchar(6) = '=',
@ConditionCriteria varchar(100) = NULL,
@ConditionalBGColor varchar(7) = '#cd5c5c',
@FontSize varchar(2) = '10'
AS
BEGIN
set nocount on;
BEGIN TRY
BEGIN TRANSACTION
--====================================================================================
-- CHECK PARAMS FOR VALID DATA
--====================================================================================
--***********************************************************
-- BEGIN @SCHEMA CHECK
--***********************************************************
--if the schema does not exist raise an error
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = @Schema) AND
@Schema IS NOT NULL
BEGIN
RAISERROR ('The specified schema does not exists. SP aborted.',10,1) WITH LOG
RETURN 99 --ERROR
END
--***********************************************************
-- BEGIN @OBJECT CHECK
--***********************************************************
--if the object does not exist raise and error
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(@object)) AND
@Object IS NOT NULL
BEGIN
--RAISEERORR WITH LOG
RAISERROR ('The specified object name does not exists. SP aborted.',10,1) WITH LOG
RETURN 99 --ERROR
END
--====================================================================================
-- GET FULL QUALIFIED NAME OF OBJECT
--====================================================================================
DECLARE @FullQualifiedName sysname
set @FullQualifiedName = '[' + DB_NAME() + '].'
set @FullQualifiedName = @FullQualifiedName + '[' + COALESCE(@schema,'dbo') + '].'
set @FullQualifiedName = @FullQualifiedName + '[' + @object + ']'
--***********************************************************
-- BEGIN @CONDITIONCOLUMN CHECK
--***********************************************************
DECLARE @ConditionColumnExists BIT
--if the condition column exists make the variable 1 else 0
IF EXISTS(
select 1
from sys.columns
where name = @ConditionColumn and
object_id = object_id(@FullQualifiedName))
AND @ConditionColumn IS NOT NULL
BEGIN
set @ConditionColumnExists = 1
END
ELSE
BEGIN
set @ConditionColumnExists = 0
END
--***********************************************************
-- BEGIN @CONDITIONCOLUMNCRITERIA CHECK
-- The below function will strip out all invalid characters
-- valid chars are letters, numbers and the
-- symbols "%","/',"\","."
--***********************************************************
IF LEN(@ConditionCriteria) > 0
BEGIN
set @ConditionCriteria = (select master.dbo.sp_RemoveChars(@ConditionCriteria))
END
--***********************************************************
-- BEGIN @FILTER CHECK
--***********************************************************
DECLARE @FilterColumnExists BIT
--if the condition column exists make the variable 1 else 0
IF EXISTS(
select 1
from sys.columns
where name = @FilterCol and
object_id = object_id(@FullQualifiedName))
AND @FilterCol IS NOT NULL
BEGIN
--filter column exists
set @FilterColumnExists = 1
END
ELSE
BEGIN
--filter column does not exist
set @FilterColumnExists = 0
END
--***********************************************************
-- BEGIN @FILTERCRITERIA CHECK
-- The below function will strip out all invalid characters
-- valid chars are letters, numbers and the
-- symbols "%","/',"\",".",”<”,”>”,”!”
--***********************************************************
IF LEN(@FilterCriteria) > 0
BEGIN
set @FilterCriteria = (select master.dbo.sp_RemoveChars(@FilterCriteria))
END
--***********************************************************
-- BEGIN @Condition Operator CHECK
--***********************************************************
IF @ConditionOperator NOT IN('=','<','>','<=','>=','LIKE','BETWEEN','<>','!=')
BEGIN
--RAISEERORR WITH LOG
RAISERROR ('The specified filter operator is invlaid. SP aborted.',10,1) WITH LOG
RETURN 99 --ERROR
END
--***********************************************************
-- BEGIN @Filter OPerator CHECK
--***********************************************************
IF @FilterOperator NOT IN('=','<','>','<=','>=','LIKE','BETWEEN','<>','!=')
BEGIN
--RAISEERORR WITH LOG
RAISERROR ('The specified filter operator is invlaid. SP aborted.',10,1) WITH LOG
RETURN 99 --ERROR
END
--***********************************************************
-- BEGIN @ALIGN CHECK
--***********************************************************
--check for valid alignments, if the align var is set wrong
--set the alignment to left
IF @Align NOT IN ('Left','Center','Right') AND @Align IS NOT NULL
BEGIN
SET @Align = 'Left'
END
--***********************************************************
-- BEGIN @FONTSIZE CHECK
--***********************************************************
IF @FontSize NOT LIKE '[0-9]'
BEGIN
SET @FontSize = '10'
END
--if the font size is greater than 30 make it 30
IF @FontSize > 30
BEGIN
SET @FontSize = '30'
END
--====================================================================================
-- DECLARE LOCAL VARIABLES
--====================================================================================
declare @table_header varchar(max),
@table_data varchar(max),
@table_footer varchar(max),
@Cols VARCHAR(max),
@sql nvarchar(max),
@table_StyleSheet varchar(max),
@params nvarchar(500),
@x XML
--====================================================================================
-- INITIALIZE LOCAL VARIABLES
--====================================================================================
set @table_header = ''
set @table_data = ''
set @table_footer = ''
SET @Cols = ''
set @sql = ''
set @table_StyleSheet = ''
set @params = N'@bgColor varchar(7)' +
CASE WHEN @FilterColumnExists = 1 THEN ', @Filter varchar(100)' ELSE '' END
SET @x = '<i>' + REPLACE( @ColList, ',', '</i><i>') + '</i>'
--***********************************************************************
-- SETUP DELIMITED COLUMN LIST, WITH TABLE DESC TAGS
--***********************************************************************
IF @ColList = 'ALL'
BEGIN
select @Cols = COALESCE(@Cols,'') +
'''<td ' + COALESCE(@Align,'LEFT') +
'>'' + CONVERT(VARCHAR(200),COALESCE(' + c.Name + ','''')) + ''</td>'' + '
from sys.columns c
where object_id = object_id(@FullQualifiedName)
order by column_id
END
ELSE
BEGIN
select @Cols = COALESCE(@Cols,'') +
'''<td Align= ' + COALESCE(@Align,'LEFT') +
'>'' + CONVERT(VARCHAR(200),COALESCE(' + c.Name + ','''')) + ''</td>'' + '
from sys.columns c
inner join(
SELECT x.i.value('.', 'VARCHAR(100)') as [ColName]
FROM @x.nodes('//i') x(i)
) as Cols
on c.name = LTRIM(RTRIM(Cols.ColName))
where object_id = object_id(@FullQualifiedName)
order by column_id
END
--====================================================================================
-- CREATE TEMP TABLE TO HOUSE RESULTS
--====================================================================================
CREATE TABLE #Results(
HTML_Seq VARCHAR(2),
TagType VARCHAR(20),
TagText VARCHAR(MAX)
)
--====================================================================================
-- BEGIN POPULATING TEMP TABLE RESULTS, WITH HTML DATA
--====================================================================================
--***********************************************************
-- BEGIN INSERT STYLESHEET
--***********************************************************
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (1,'STYLESHEET','<style type="text/css">');
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (1,'STYLESHEET','table {font-size: ' + COALESCE(@FontSize,'10') + 'pt;}');
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (1,'STYLESHEET','</style>');
--***********************************************************
-- BEGIN INSERT TABLE HEADER
--***********************************************************
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (2,'HEADER','<HTML><BODY><table border=1 cellpadding=5 cellspacing=1>');
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (2,'HEADER','<tr bgcolor="' + COALESCE(@HeaderBGColor,'#FFFFFF') + '">');
IF @ColList = 'ALL'
BEGIN
INSERT INTO #Results (HTML_Seq,TagType,TagText)
select 2, 'HEADER', '<th>' + c.name + '</th>'
from sys.columns c
where object_id = object_id(@FullQualifiedName)
order by column_id
END
ELSE
BEGIN
INSERT INTO #Results (HTML_Seq,TagType,TagText)
select 2, 'HEADER', '<th>' + c.name + '</th>'
from sys.columns c
inner join(
SELECT x.i.value('.', 'VARCHAR(100)') as [ColName]
FROM @x.nodes('//i') x(i)
) as Cols
on c.name = LTRIM(RTRIM(Cols.ColName))
where object_id = object_id(@FullQualifiedName)
order by column_id
END
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (2, 'HEADER','</tr>');
--***********************************************************
-- BEGIN INSERT TABLE BODY
--***********************************************************
SET @sql = N'insert into #Results (HTML_Seq,TagType,TagText)
select ''3'', ''BODY'', ' +
case when @ConditionColumnExists = 1 then
'case when [' + COALESCE(@ConditionColumn,'') + '] ' + @ConditionOperator + '''' + @ConditionCriteria + ''' then
''<tr bgcolor='' + '''' + COALESCE(@bgColor,''"#FFFFFF"'') + '''' + ''>'' + '
+ COALESCE(@Cols,'') + ' + ''</tr>''
else ''<tr>'' + ' + COALESCE(@Cols,'') + ' + ''</tr>''
end'
else
'''<tr>'' + ' + COALESCE(@Cols,'') + ' + ''</tr>'''
end + '
From ' + @FullQualifiedName +
case when @FilterColumnExists = 1 THEN
' WHERE ' + COALESCE(@FilterCol,'') + space(1) + @FilterOperator + space(1) + '@Filter'
ELSE '' END
IF @FilterColumnExists = 0
BEGIN
EXECUTE sp_executesql @sql
,@params
,@bgColor = @ConditionalBGColor
END
IF @FilterColumnExists = 1
BEGIN
EXECUTE sp_executesql @sql
,@params
,@bgColor = @ConditionalBGColor
,@Filter = @FilterCriteria
END
--print @Cols
--print @FilterCriteria
--***********************************************************
-- BEGIN INSERT TABLE FOOTER
--***********************************************************
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (4, 'FOOTER','</table><BODY><HTML>');
--====================================================================================
-- FINAL SELECT TO RETURN THE NEEDED DATA
--====================================================================================
select TagText
from #Results
order by HTML_Seq
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION
END CATCH
END
GO
--====================================================================================
-- Mark the sp as a system object, so you can call it from any db
-- and get the local system metadata.
--====================================================================================
EXEC sys.sp_MS_marksystemobject 'sp_Create_HTML_Rpt'
GO
Now that we have our HTML code, we can create the stored procedure that emails the report. Note: This stored procedure is just a wrapper that calls sp_Create_HTML_Rpt, along with sp_send_dbmail.
Parameters:
- @Email_To – Email addresses to send the report
- @Email_Body – The text of the email, which proceeds the HTML report.
- @Email_Subject – The subject of the email
- All remaining parameters have been discussed
USE master
GO
IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'sp_Mail_HTML_Rpt')
BEGIN
DROP PROCEDURE sp_Mail_HTML_Rpt;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_Mail_HTML_Rpt
--@Email_Profile VARCHAR(25),
@Email_To VARCHAR(100),
@Email_Body VARCHAR(max),
@Email_Subject VARCHAR(100),
@schema varchar(100) = 'dbo',
@object varchar(100),
@ColList varchar(max) = 'ALL',
@FilterCol VARCHAR(100) = NULL,
@FilterOperator varchar(6) = '=',
@FilterCriteria varchar(100) = NULL,
@HeaderBGColor VARCHAR(7) = '#dcdcdc',
@Align Varchar(10) = 'LEFT',
@ConditionColumn varchar(100) = NULL,
@ConditionOperator varchar(6) = '=',
@ConditionCriteria varchar(100) = NULL,
@ConditionalBGColor varchar(7) = '#cd5c5c',
@FontSize varchar(2) = '10'
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--==========================================================================
-- DECLARE LOCAL VARIABLES
--==========================================================================
DECLARE @Email_Query NVARCHAR(500),
@db sysname
--==========================================================================
-- INITIALIZE LOCAL VARIABLES
--==========================================================================
SET @db = db_name()
--********************************************************
-- Execute sp to create HTML report
--********************************************************
SET @Email_Query =
N'EXECUTE sp_Create_HTML_Rpt
@schema = ''' + COALESCE(@schema,'dbo') + '''
,@object = ''' + @object + '''
,@ColList = ''' + COALESCE(@ColList,'ALL') + '''
,@FilterCol = ''' + COALESCE(@FilterCol,'') + '''
,@FilterOperator = ''' + COALESCE(@FilterOperator,'=') + '''
,@FilterCriteria = ''' + COALESCE(@FilterCriteria,'') + '''
,@HeaderBGColor = ''' + COALESCE(@HeaderBGColor,'#dcdcdc') + '''
,@Align = ''' + COALESCE(@Align,'LEFT') + '''
,@ConditionColumn = ''' + COALESCE(@ConditionColumn,'') + '''
,@ConditionOperator = ''' + COALESCE(@ConditionOperator,'=') + '''
,@ConditionCriteria = ''' + COALESCE(@ConditionCriteria,'') + '''
,@ConditionalBGColor = ''' + COALESCE(@ConditionalBGColor,'#cd5c5c') + '''
,@FontSize = ''' + COALESCE(@FontSize,'10') + ''''
--==========================================================================
-- EXECUTE SEND MAIL TASK
--==========================================================================
--you can make an html attachment
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = @Email_Profile,
@recipients = @Email_To,
@body = @Email_Body,
@query = @Email_Query,
@execute_query_database = @db,
@attach_query_result_as_file = 0,
@query_result_header = 0,
@exclude_query_output = 1,
@subject = @Email_Subject,
@body_format = 'HTML',
@importance = 'NORMAL',
@append_query_error =1,
@query_no_truncate = 1
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION
END CATCH
END
Now that you have the code you can execute it by issuing a statement similar to this.
EXECUTE [sp_Mail_HTML_Rpt]
--@Email_Profile = null --db mail email profile (not needed)
@Email_To = 'ahaines@somedomain.com' --send email to
,@Email_Body = 'Attached is the daily scheduled job report. Please review. <br> <br>' --body text
,@Email_Subject = 'Schedule Job Report' --subject
,@schema = 'dbo' --object schema
,@object = 'vw_ScheduleJobReport' --object name
,@ColList = 'ALL' -- specify column list comma delimited. All is default
,@FilterCol = null --column to filter data on
,@FilterOperator = null --filter operand
,@FilterCriteria = null -- filter criteria
,@HeaderBGColor = NULL --header background color
,@Align = null --detail align
,@ConditionColumn = 'descr' --column for conditional formatting
,@ConditionOperator = '=' --conditional operator
,@ConditionCriteria = 'FAILED' --criteria to highlight column
,@ConditionalBGColor = 'RED' --conditional background color
,@FontSize = NULL -- detail font size
There you have it. A stored procedure that will take a given table/view and automatically generate and email an HTML report. As you can see this code is easily customizable and I invite you to do so.
2 comments:
Hi sir – thank you so much for this great job you did combining HTML report and E-mail. I don’t know much about SQL and DB but I need it in my application. I was able to understand the logic very well – thank you. You also taught me about the risk of SQL injection attack.
When I tested the SP and the execution, I got the error “The specified object name does not exists. SP aborted. Msg 266, Level 16, State 2, Procedure sp_Create_HTML_Rpt, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.” in my Express 2012. Since I am not familiar with the “commit transaction” in relation with “begin”, I don’t know how to fix this. I would appreciate any help.
I was also looking forwards to learn about the daily scheduled job report, thinking you would cover the sp_procoption. But, I am sure I misunderstood this aspect of your excellent article.
Paul
Hi sir – thank you so much for this great job you did combining HTML report and E-mail. I don’t know much about SQL and DB but I need it in my application. I was able to understand the logic very well – thank you. You also taught me about the risk of SQL injection attack.
When I tested the SP and the execution, I got the error “The specified object name does not exists. SP aborted. Msg 266, Level 16, State 2, Procedure sp_Create_HTML_Rpt, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.” in my Express 2012. Since I am not familiar with the “commit transaction” in relation with “begin”, I don’t know how to fix this. I would appreciate any help.
I was also looking forwards to learn about the daily scheduled job report, thinking you would cover the sp_procoption. But, I am sure I misunderstood this aspect of your excellent article.
Paul
Post a Comment