January 19, 2009 at 9:14 am
Hi, im trying to send query results as HTML mail. i will setup up a job to run this daily.
its nothing complicated, but im having trouble formatting the resultset in html.
any help will be appreciated
is there a way to use XSL and XML?
DECLARE @Temp1 TABLE
(
ClientName VARCHAR(50),
AcqAll INT,
AcqOk INT,
AcqBad INT,
AcqPend INT,
RegAll INT,
RegOk INT,
RegBad INT
)
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test1', 1,2,3,4,5,6,7
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test2', 10,20,30,40,50,60,70
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test3', 101,201,301,401,501,601,701
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test4', 100,200,300,400,500,600,700
SELECT * FROM @Temp1
January 19, 2009 at 9:36 am
While you certainly could add a FOR XML to your query to spit it out in XML, actually applying an XSL transform to it in order to e-mail it is something you want to do outside of T-SQL. That's something best done through an EXTERNAL .Net app, although it can certainly be done using a .Net stored proc as well if required.
There's nothing built-in to do that for you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2009 at 1:58 pm
can anyone help with an html mailer?
January 20, 2009 at 1:08 am
is it possible to reference external style sheet and images for a sql mailer?
January 20, 2009 at 1:29 am
yisaaacs (1/19/2009)
Hi, im trying to send query results as HTML mail. i will setup up a job to run this daily.its nothing complicated, but im having trouble formatting the resultset in html.
any help will be appreciated
is there a way to use XSL and XML?...
Using a database mail you can send the mail in any format.(HTML/text) check for BOL for moer help on database mail.
Abhijit - http://abhijitmore.wordpress.com
January 20, 2009 at 1:37 am
i have the html mail up and running, but im hoping i would be able to reference an external css file with images sitting on a server somewhere.
is this at all possible?
January 21, 2009 at 12:59 am
As Matt points out FOR XML is your friend here if you insist on doing this on the DB server. Here's a typical approach, suitable for dumping directly into a queue table for later sending, or you could loop through it with a cursor/WHILE (ugh) and send the emails directly. Note that this is a correlated subquery and assumes you want a seperate row per ClientName, so performance will suffer on larger tables. Tweak at will if you don't need this correlation:
/* OP sample DDL */
DECLARE @Temp1 TABLE
(
ClientName VARCHAR(50),
AcqAll INT,
AcqOk INT,
AcqBad INT,
AcqPend INT,
RegAll INT,
RegOk INT,
RegBad INT
)
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test1', 1,2,3,4,5,6,7
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test2', 10,20,30,40,50,60,70
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test3', 101,201,301,401,501,601,701
INSERT INTO @Temp1 (ClientName, AcqAll, AcqOk, AcqBad,AcqPend,RegAll,RegOk,RegBad)
SELECT 'test4', 100,200,300,400,500,600,700
SELECT * FROM @Temp1
/* boilerplate HTML template variables */
declare @Header nvarchar(max)
declare @Footer nvarchar(max)
declare @CrLf nchar(2)
select @CrLf = nchar(13) + nchar(10)
select @Header =
N'{html}' + @CrLf +
N'{head}' + @CrLf +
N'{title}Some Title{/title}' + @CrLf +
N'{style type="text/css" media="all"}@import "http://www.myserver.com/style/my.css";{/style}' + @CrLf +
N'{/head}' + @CrLf +
N'{body}' + @CrLf +
N'{table class="MyOutputTable"}' + @CrLf +
N'{thead}{tr}{td}ClientName{/td}{td}AcqAll{/td}{td}AcqOk{/td}{td}AcqBad{/td}' +
N'{td}AcqPend{/td}{td}RegAll{/td}{td}RegOK{/td}{td}RegBad{/td}{/tr}{/thead}' + @CrLf +
N'{tbody}' + @CrLf
select @Footer =
N'{/tbody}' + @CrLf +
N'{/table}' + @CrLf +
N'{/body}' + @CrLf +
N'{/html}' + @CrLf
/* FOR XML transform */
select
ClientName,
(select
@Header +
cast((select
td = ClientName, N'',
td = convert(nvarchar,AcqAll), N'',
td = convert(nvarchar,AcqOk), N'',
td = convert(nvarchar,AcqBad), N'',
td = convert(nvarchar,AcqPend), N'',
td = convert(nvarchar,RegAll), N'',
td = convert(nvarchar,RegOK), N'',
td = convert(nvarchar,RegBad), N''
from @Temp1
where ClientName = a.ClientName
for xml path(N'tr'), type
) as nvarchar(max)) + @CrLf +
@Footer
) as [HTMLBody]
from @Temp1 a
NOTE: Replace all of the braces ( { and } ) with the appropriate greater-than / less-than symbols.
Regards,
Jacob
January 21, 2009 at 3:46 pm
this may be of use (posted previously on another thread), using FOR XML:
http://www.sqlservercentral.com/Forums/Topic616796-1291-1.aspx#bm618171
🙂
January 22, 2009 at 3:45 pm
Hi,
I had a similar requirement. Basically i had to email the statistics every morning to users.
I used SSIS to accomplish this as follows:
1) By using a FOR XML clause i returned the XML resultset in a Variable in a Execute SQL Task.
Note that we need to set the ResultSet type to 'XML'.
2) Converted the XML results to HTML by using the XML Transformation task with specifying the xsl file as the Second Operand. The HTML results would be again stored to a Variable.
3) Email the results by using the HTML variable as a Body. I actually used a Script task to send email.
4) Schedule the SSIS package as a SQL Job.
This works perfectly fine for me. This is just one of the methods to accomplish the same thing.
Amol
Amol Naik
January 22, 2009 at 11:56 pm
i tried creating a ssis package in business intelligence studio but unfortunately i dont know my way around it and i dont have time to play around with it.
anyway, i retrieve the data spurn it into xml and place into my html. I created style to format the mail beautifully and all is good in my world:)
in sql server 2000, i could create packages on the fly in Enterprise Manager, where do i create these packages in sql server 2005
January 23, 2009 at 12:03 pm
You need to use the Business Intelligence Development Studio for creating SSIS Packages. This comes along with SQL Server 2005. You need to create a SSIS Solution and then start building your packages, build them and deploy the SQL Server msdb database.
Amol
Amol Naik
March 24, 2009 at 7:44 am
As referenced in your original post here is a article written that uses XSL transformation.
It may be what your looking for...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply