June 4, 2017 at 2:44 am
Comments posted to this topic are about the item Practical usage of SQLCLR: Building QueryToHtml function
June 5, 2017 at 1:20 am
Thank you Darko for this most excellent idea to build upon.
This kind of article is why i love sqlservercentral so much !
June 5, 2017 at 5:50 am
Once again - Thank you so much Microsoft for ditching sp_makewebtask.
12 years on - people are still trying to reinvent it.
_____________
Code for TallyGenerator
June 5, 2017 at 6:40 am
Well done. I appreciate the effort required to put an article like this together let alone the coding to build the tool itself.
I know a guy that hangs on this site whose name rhymes with Reff Snowden who would appreciate a full-featured replacement for the web task sp. There is a reason why Microsoft dropped it, however. There are some other pieces of the language that are remnants of its client-server roots that I'd wish they'd drop as well.
In a sense this brings back a popular tool but in a sense it's regressive in terms of where the platform and the industry are going.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 5, 2017 at 6:52 am
I made a similar proc that uses SQL but unlike the original query in this article, is completely dynamic.
June 5, 2017 at 7:40 am
Here is a simple stored procedure that will do basically the same thing:CREATE PROCEDURE [dbo].[spHtmlFromTableEmail]
@database varchar(100),
@schema varchar(5)='dbo',
@table_name varchar(100),
@columns_list varchar(8000)='ALL',
@subject varchar(100)='',
@body varchar(max)='',
@email_list varchar(1000),
@cc_list varchar(1000)=NULL,
@mail_profile varchar(100),
@send char(1)='N'
as
declare
@headers nvarchar(max)='',
@content nvarchar(max)='',
@sql nvarchar(max)=''
if object_id(@database+'.'+@schema+'.'+@table_name) is null
begin
DECLARE @msg NVARCHAR(MAX);
SET @msg = N'Table doesn''t exist: ' + @database+'.'+@schema+'.'+@table_name
RAISERROR(@msg,16,1);
return
end
-- if the columns list is empty or ALL then build the list from INFORMATION_SCHEMA
if isnull(@columns_list,'ALL')='ALL'
begin
SET @columns_list=''
select @sql=
'select
@content_out=@content_out+COLUMN_NAME+'',''
from
'+@database+'.INFORMATION_SCHEMA.COLUMNS
where
TABLE_NAME='''+@table_name+''''
exec sp_executesql @sql, N'@content_out nvarchar(max)='''' OUTPUT', @content_out=@columns_list output
select
@sql='',
@columns_list=left(@columns_list,len(@columns_list)-1)
end
/*-- ### debug purpsoe only
print @sql
print @columns_list
return
--*/
-- buld the HTML header and the sql for the table content
select
@headers=@headers+char(9)+'<th> '+ cols.Elem+ ' </th>'+char(13)+char(10),
@sql=@sql+char(13)+char(10)+char(9)+'ISNULL(cast(['+cols.Elem+'] as varchar(max)),'''') as '''+case when cols.Elem='row_css_class' and cols.ElemID=1 then cols.Elem else 'td' end +''','''','
FROM
[zb_dba_maint].[dbo].fnParseStringToSet(@columns_list,',') cols
select
@headers='<tr>'+char(13)+char(10)+@headers+'</tr>'+char(13)+char(10),
@sql='SELECT '+left(@sql,len(@sql)-1)+char(13)+char(10)+
'FROM '+char(13)+char(10)+char(9)+@database+'.'+@schema+'.'+@table_name+char(13)+char(10)+
'FOR XML PATH(''tr''), ELEMENTS'
select
@sql='SET @content_out =
(
SELECT
replace(
replace(
replace(
replace(
replace(
replace(
replace(tbl,''<tr><row_css_class>Monday</row_css_class>'',''<tr class="monday">'')
,''<tr><row_css_class>Tuesday</row_css_class>'',''<tr class="tuesday">'')
,''<tr><row_css_class>Wednesday</row_css_class>'',''<tr class="wednesday">'')
,''<tr><row_css_class>Thursday</row_css_class>'',''<tr class="thursday">'')
,''<tr><row_css_class>Friday</row_css_class>'',''<tr class="friday">'')
,''<tr><row_css_class>Saturday</row_css_class>'',''<tr class="saturday">'')
,''<tr><row_css_class>Sunday</row_css_class>'',''<tr class="sunday"'') as htm
from
(
SELECT CAST(('+@sql+') AS VARCHAR(MAX))
) q (tbl))
'
--select @sql
--return
exec sp_executesql @sql, N'@content_out nvarchar(max) OUTPUT', @content_out=@content output
select
@body='
<html>
<head>
<STYLE TYPE="text/css">
<!--
BODY
{
font-family:tahoma;
font-size:11;
}
table, td, th {
margin: 1;
padding: 1;
border: 1px solid gray;
}
table {
border-collapse: collapse;
border-spacing: 1;
}
TD
{
color:black;
}
TH
{
background-color:#3390CC;
color:white;
align=left;
}
TR.monday
{
background-color: #CCFFFF; color: black;
}
TR.tuesday
{
background-color: #FFCCFF; color: black;
}
TR.wednesday
{
background-color: #CCFF99; color: black;
}
TR.thursday
{
background-color: #FFCC66; color: black;
}
TR.friday
{
background-color: #CCFFCC; color: black;
}
TR.saturday
{
background-color: #FFCCCC; color: black;
}
TR.sunday
{
background-color: #99CCFF; color: black;
}
A:link{color:blue}
A:visited{color:brown}
-->
</STYLE>
<head>
<body>
<h5>'+ISNULL(@body,'')+'</h5>
<h5>Source: '+convert(varchar, SERVERPROPERTY('ServerName'))+'.'+db_name()+'.'+@schema+'.'+@table_name+' content '+cast(GETDATE()as varchar(12))+'</h5>
<table border="1">
'+replace(@headers,'<th> row_css_class </th>','')+@content+'
</table>
</body>
</html>
'
if @subject like '%ERROR%'
set @body=replace(@body,'background-color:#3390CC;','background-color:red;')
if @send='Y'
begin
select
@subject=ISNULL(@subject,convert(varchar, SERVERPROPERTY('ServerName'))+' Table '+db_name()+'.'+@schema+'.'+@table_name+' content '+cast(GETDATE()as varchar(12)))
EXECUTE msdb.dbo.sp_send_dbmail
@recipients=@email_list,
@copy_recipients=@cc_list,
@body_format = 'HTML',
@body=@body,
@subject =@subject,
@profile_name =@mail_profile
end
else
select replace(replace(replace(replace(
@body, '<tr>',char(13)+char(10)+char(9)+char(9)+'<tr>'),
'</tr>',char(13)+char(10)+char(9)+char(9)+'</tr>'),
'<td>',char(13)+char(10)+char(9)+char(9)+char(9)+'<td>'),
'<th>',char(9)+char(9)+'<th>')
If you have any query you can use SELECT ... INTO <table_name> feature and then the use the stored procedure above to send an email in HTML format with the <table_name> table just created. After that you can delete the table. in the @columns_list parameter you pass a list of the columns you want to build the result separated by comma (,). The default for this parameter is 'ALL', which will use all the columns.
-- Update June 6th.
As someone noticed, the stored procedure above uses some functions that I forgot to provide. Here they are:
CREATE FUNCTION [dbo].[fnTally]()
RETURNS TABLE --WITH SCHEMABINDING
AS
/*******************************************************************************\
Function : fnTally
Purpose : returns a set with numbers from 1 to 10,000
to be used in parsing and sequential data generation whithout loop
Parameters : no parameters
Invoke :
select * from [dbo].[fnTally]()
select N from [dbo].[fnTally]()
select substring('abcdef',N,1) as chr from [dbo].[fnTally]() where N<len('abcdef') -- parsing a string
select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]() --gets dates for about 30 years
Author : AdrianBT - 2013-03-18
\*******************************************************************************/
RETURN
WITH
E1(N) AS
( --10E+1 or 10 rows
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS
( --10E+2 or 100 rows
SELECT 1 FROM E1 a, E1 b
),
E4(N) AS
( --10E+4 or 10,000 rows max
SELECT 1 FROM E2 a, E2 b
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
;
GO
CREATE FUNCTION [dbo].[fnParseStringToSet]
(
@str VARCHAR(8000),
@sep CHAR(1)
)
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT KILLS PERFORMANCE!
RETURNS TABLE
AS
/*******************************************************************************\
Function : fnParseStringToSet
Purpose : parses a string by a separator and returns a two columns set with
each element and its position; uses the fnTally for parsing
Parameters : @str - the string to parse
@sep - the separator character
Invoke :
select * from [dbo].[fnParseStringToSet]('ab cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef',' ')
select tsk.ElemId, tsk.Elem as QATaskId,tme.Elem as EndTime from
[dbo].[fnParseStringToSet]('10,20,30,40,50',',') tsk
left join [dbo].[fnParseStringToSet]('17:10,17:11,17:12',',') tme
on tme.ElemId=tsk.ElemId
Author : AdrianBT - 2013-03-18
\*******************************************************************************/
RETURN
with cteStart(N1) AS
(--returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 0 UNION ALL
SELECT t.N+1 FROM [dbo].[fnTally]() t WHERE SUBSTRING(@str,t.N,1) = @sep and t.N<=len(@str)
),
cteLen(N1,L1) AS
(--returns start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@sep,@str,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--so the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT
ROW_NUMBER() OVER(ORDER BY l.N1) as ElemID,
SUBSTRING(@str, l.N1, l.L1) as Elem
FROM
cteLen l
;
GO
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
June 6, 2017 at 12:09 am
I wrote something similar in my current job, but using purely dynamic SQL and using a table (in any database, including temp tables) as the source rather than a query which avoids most of the issues with parameters etc (then a colleague did the Query-> Table->HTML on top of mine for dealing with most of the quick cases. Over time it's picked up some handy functions for filtering both rows and columns as well as highlighting data that matches key conditions. It would be nice to get something like this in SQL Server itself though, it's such a common thing to require.
On a slightly different note, I'd suggest not using Excel interop on your SQL box like that. Excel isn't really built for non-interactive use and if you aren't careful is very likely to pop up a dialog box that nobody will ever see, blocking your SQL whilst it waits for interaction. It's better to either generate Excel XML directly or go via the Office File Format DLLs, which are much more robust in that matter (personally I tend to just go straight to SSIS at that point).
June 6, 2017 at 12:42 am
Theo Ekelmans - Monday, June 5, 2017 1:20 AMThank you Darko for this most excellent idea to build upon.
This kind of article is why i love sqlservercentral so much !
Thank you Theo. Negative comments are welcome too.
June 6, 2017 at 12:45 am
Orlando Colamatteo - Monday, June 5, 2017 6:40 AMWell done. I appreciate the effort required to put an article like this together let alone the coding to build the tool itself.I know a guy that hangs on this site whose name rhymes with Reff Snowden who would appreciate a full-featured replacement for the web task sp. There is a reason why Microsoft dropped it, however. There are some other pieces of the language that are remnants of its client-server roots that I'd wish they'd drop as well.In a sense this brings back a popular tool but in a sense it's regressive in terms of where the platform and the industry are going.
Thank you Orlando 🙂 ! You said everything exactly. I have nothing to add. Thanks for the engagement.
June 6, 2017 at 12:49 am
Sergiy - Monday, June 5, 2017 5:50 AMOnce again - Thank you so much Microsoft for ditching sp_makewebtask.12 years on - people are still trying to reinvent it.
Thank you Sergiy for your response 🙂!
Something like that. By the way, we are trying to detect the SQLCLR that has been there for so many years and is not so popular.
June 6, 2017 at 3:21 am
In the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
But I will repeat it is not the topic of this article, nor would it ever be my choice.
June 6, 2017 at 8:08 am
What is "[zb_dba_maint].[dbo].fnParseStringToSet(@columns_list,',') " and where is that coming from?
Thank you
June 6, 2017 at 8:16 am
DLight101 - Tuesday, June 6, 2017 8:08 AMWhat is "[zb_dba_maint].[dbo].fnParseStringToSet(@columns_list,',') " and where is that coming from?
Thank you
🙂 Sorry about that. It is a function I built which takes a string and a separator character, parse the string and returns a set with all the values separated by the separator. This function on its turn uses another function, which I will both post below. You should create these functions in the same database you have the spHtmlFromTableEmail stored procedure and than remove the database name reference inside it:
CREATE FUNCTION [dbo].[fnTally]()
RETURNS TABLE --WITH SCHEMABINDING
AS
/*******************************************************************************\
Function : fnTally
Purpose : returns a set with numbers from 1 to 10,000
to be used in parsing and sequential data generation whithout loop
Parameters : no parameters
Invoke :
select * from [dbo].[fnTally]()
select N from [dbo].[fnTally]()
select substring('abcdef',N,1) as chr from [dbo].[fnTally]() where N<len('abcdef') -- parsing a string
select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]() --gets dates for about 30 years
Author : AdrianBT - 2013-03-18
\*******************************************************************************/
RETURN
WITH
E1(N) AS
( --10E+1 or 10 rows
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS
( --10E+2 or 100 rows
SELECT 1 FROM E1 a, E1 b
),
E4(N) AS
( --10E+4 or 10,000 rows max
SELECT 1 FROM E2 a, E2 b
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
;
GO
CREATE FUNCTION [dbo].[fnParseStringToSet]
(
@str VARCHAR(8000),
@sep CHAR(1)
)
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT KILLS PERFORMANCE!
RETURNS TABLE
AS
/*******************************************************************************\
Function : fnParseStringToSet
Purpose : parses a string by a separator and returns a two columns set with
each element and its position; uses the fnTally for parsing
Parameters : @STR - the string to parse
@sep - the separator character
Invoke :
select * from [dbo].[fnParseStringToSet]('ab cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef cd ef',' ')
select tsk.ElemId, tsk.Elem as QATaskId,tme.Elem as EndTime from
[dbo].[fnParseStringToSet]('10,20,30,40,50',',') tsk
left join [dbo].[fnParseStringToSet]('17:10,17:11,17:12',',') tme
on tme.ElemId=tsk.ElemId
Author : AdrianBT - 2013-03-18
\*******************************************************************************/
RETURN
with cteStart(N1) AS
(--returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 0 UNION ALL
SELECT t.N+1 FROM [dbo].[fnTally]() t WHERE SUBSTRING(@str,t.N,1) = @sep and t.N<=len(@str)
),
cteLen(N1,L1) AS
(--returns start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@sep,@str,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--so the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT
ROW_NUMBER() OVER(ORDER BY l.N1) as ElemID,
SUBSTRING(@str, l.N1, l.L1) as Elem
FROM
cteLen l
;
GO
Now you will use for the example you posted:
[dbo].fnParseStringToSet(@columns_list,',')
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
June 6, 2017 at 8:34 am
Darko Martinovic - Tuesday, June 6, 2017 3:21 AMIn the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
But I will repeat it is not the topic of this article, nor would it ever be my choice.
We understand that, with the reserve that string manipulation in SQL server is not a problem at all, but at the same time I think people deserve to know that the same things can be achieved in a pure SQL approach, which is also much, much simpler than using CLR feature. If, however, the things become over complicated the CLR method might be preferable although in that case one should leave altogether the SQL Server environmant and bult an external module using a programming language of their choice.
As a general idea the CLR processes are using a lot of memory and CPU power, which in some cases are in the detriment of performace.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
June 6, 2017 at 11:22 am
the sqlist - Tuesday, June 6, 2017 8:34 AMDarko Martinovic - Tuesday, June 6, 2017 3:21 AMIn the introduction I forgot to mention that dynamic T-SQL is one of the methods to get valid HTML.
My opinion is that string manipulation is not a T-SQL advantage. This is the area where .NET takes precedence.
I am convinced that all these solutions are presented by colleagues through dynamic T-SQL's good.
But I will repeat it is not the topic of this article, nor would it ever be my choice.We understand that, with the reserve that string manipulation in SQL server is not a problem at all, but at the same time I think people deserve to know that the same things can be achieved in a pure SQL approach, which is also much, much simpler than using CLR feature. If, however, the things become over complicated the CLR method might be preferable although in that case one should leave altogether the SQL Server environmant and bult an external module using a programming language of their choice.
As a general idea the CLR processes are using a lot of memory and CPU power, which in some cases are in the detriment of performace.
You would be surprised! In many situation, spetially when parallel execution has involved, CLR performs better.
I'm using QueryToHtml function during years and it performs well, it is easy to use and adopt.
When you are in house DBA, probably you will not use CLR. But, if you are software vendor with thousend of customers and thousend of installations, CLR is much better solution.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply