David McKinney (9/6/2010)
Jeff Moden (9/4/2010)
And did you see the size of the style sheet that was posted on this thread?....The addition of a style sheet in the discussion made it only that much more complicated.....Maybe an article on the subject of simplicity is in order. 😛Hi Jeff,
As for the size of the stylesheet, it's roughly the same size as the html document it produces minus the data - which is as it should be.
I think that the use of xml and xsl is at the absolute centre of what the article was about (and not an addition to the discussion.)
If there are corners to be cut, I'd suggest (and I can hear you groan as I write this) a 2 line CLR function which takes an xsl, xml and returns the transformed document. That enables to you to do all this without troubling SSIS.
I have to say that one of my top pet hates is seeing html constructed with T-sql.
I note, Jeff, that you have a preference - and it is your right - for doing everything that can possibly be done with SQL with SQL. The result is that you can do some pretty crazy things with SQL with leave me for one breathless if not speechless. But I would argue that for generating html especially sophisticated html is either the job of an ASP application or why not take advantage of the xml functionalities in SQL and transform the result with XSL, as Paul has done. (Horses for courses?)
I include a link to articles [/url]I've written in a similar vein - which I fear you may hate! But just so you know precisely where I'm coming from!
Best regards,
David McKinney.
Well said and understood. Please keep in mind that I'm not trying to bad mouth anyone or any technique. I just don't see the need for the complexity of it all even if you don't do it through SSIS. You don't need to keep track of code for a CLR nor instantiate it. You don't need to keep track of a style sheet nor worry about if the DBA or OPs group will actually let me have access to it. You don't need to generate XML and then have a script convert it to HTML nor do you need to keep track of a script. There are a lot of things you don't need to worry about if you can do it all in T-SQL because all of the code is all in one place. Oddly enough, with the advent of FOR XML PATH, you can easily create the necessary HTML in T-SQL and the whole thing is shorter than most style sheets. Here's an example I created for another post. Please know that the only thing I wasn't able to test in this sproc was the actual call to sp_send_dbmail.
CREATE PROCEDURE dbo.SendProtocol
/**********************************************************************************************************************
Purpose:
Send an email for all open Protocols for a give Study Manager ID.
(This IS a part of the solution)
**********************************************************************************************************************/
--===== Declare I/O Parameters
@pStudyManagerID INT
AS
--===== Environmental Presets
SET NOCOUNT ON;
--===== Declare local variables
DECLARE @Body NVARCHAR(MAX),
@Email NVARCHAR(50),
@FirstName NVARCHAR(15)
;
--===== Get the email address and name for the given study manager id
SELECT @FirstName = FirstName,
@EMail = Email
FROM #Staff
WHERE StaffID = @pStudyManagerID
;
--===== Create the body of the message including a nicely formatted table of the protocols
SELECT @Body =
------- Create the table and the table header. (Easily readable HTML)
'
<html>
<body>
<p>Dear ' + @FirstName + ',</p>
<p>The following table contains a list of your open Protocols.</p>
<table border="1" cellspacing="0" style="text-align:center">
<caption>Protocols</caption>
<tr style="background-color:AliceBlue"><th>Protocol No</th><th>Open to Enrollment Date</th><th>Enrollment Goal</th></tr>
'
------- Create the rest of the table. Filled in from data in the table.
+ SPACE(8)
+ REPLACE(--This just indents each row to make rows in the HTML apparent and easy to read
CAST((SELECT td = ProtocolNo, N'', --<td></td> = "data" element in a row
td = CONVERT(NCHAR(11), OpenToEnrollDate, 106), N'',
td = CAST(EnrollmentGoal AS NVARCHAR(10)), N''
FROM #Protocol
WHERE IsOpen = 1
AND StudyManagerID = @pStudyManagerID
FOR XML PATH('tr'),TYPE --<tr></tr> = row encapsulation
)AS NVARCHAR(MAX))
,'</tr>','</tr>'+CHAR(10)+SPACE(8))
------- Finalize the HTML
+ '
</table>
</body>
</html>'
;
--===== All set. Send the email.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifier_Profile',
@recipients = @EMail,
@subject = 'Your open Protocols',
@body = @Body
;
GO
I realize that you good folks can't test that code because you don't have the data, etc, etc. With that in mind, here's what one run of the code looks like in HTML...
<html>
<body>
<p>Dear Donald,</p>
<p>The following table contains a list of your open Protocols.</p>
<table border="1" cellspacing="0" style="text-align:center">
<caption>Protocols</caption>
<tr style="background-color:AliceBlue"><th>Protocol No</th><th>Open to Enrollment Date</th><th>Enrollment Goal</th></tr>
<tr><td>BRE150</td><td>26 Aug 2010</td><td>250</td></tr>
<tr><td>LUN120</td><td>26 Aug 2010</td><td>50</td></tr>
</table>
</body>
</html>
Here's what the email body looks like for a given ID when received...
As a bit of a side bar, people keep telling me the ol' "Jeff, to a hammer, everything is a nail" and they proceed to lecture on the subject of why I shouldn't be using T-SQL for "everything". I suggest turning this around a bit. Learn how to identify what a nail actually is and then understand that you don't need a thousand pound air compressor, air hose, electricity, extension cords, 32 types of nail guns, and a truck to get it all there when it's just a brad that you're trying to hammer in. 😛 😉 Heh... to a brad, everything is a hammer. :hehe:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)