August 22, 2012 at 8:20 am
Hi, I've created numerous emails which send the results, tabulated in the body of the email, it looks good. However when the query has a few wider columns it obviously doesn't fit on the email body page and squeezes it up. Can this be fixed or is there another way to send this HTML style format in an email?
Thanks
August 22, 2012 at 8:37 am
it depends;
you can use css to define the size fo the td cells(width: 210px;min-width: 100px;), or set them to not allow wrapping at all, depending on your needs (white-space:nowrap;), and that might help address the appearance.
So a piece of that is knowing how you want it to look, and addressing the bad layouts when you visually inspect it.
some email programs are going to force the contents to fit within a certain size no matter the css or html tags.
Lowell
August 22, 2012 at 8:44 am
a very basic example of including extra tags:
DECLARE @HTMLBody varchar(max)
Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
name As
,
product As
,
provider As
,
data_source As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements)
Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
Set @HTMLBody= Replace(@HTMLBody,'_x0022_','"')
Set @HTMLBody= Replace(@HTMLBody,'_x003B_',';')
Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')
Select '<table>' + @HTMLBody + '</table>'
Lowell
August 22, 2012 at 9:04 am
Thanks so much for your help Lowell...almost there i think. Still getting syntax errors. My code is a little different from yours but I've added where I think it appropriate...
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H2>DBA Jira open Calls</H2>' +
N'<table border="1">' +
N'<tr>' +
N'<th>Issue ID</th>' +
N'<th>Summary</th>' +
N'<th>Call Date</th>' +
N'<th>Priority</th>' +
N'<th>Status</th>' +
N'<th>Assignee</th>' +
N'</tr>' +
cast ( (
SELECTtd = pkey,
'',
summary As
,
'',
td = convert(char(17),created,13),
'',
td = p.pname,
'',
td = iss.pname,
'',
td = assignee,
''
from jiraissueji
..........................................
However I then get...
Msg 6846, Level 16, State 4, Line 3
XML name space prefix 'TD style="width' declaration is missing for FOR XML column name 'TD style="width: 210px;min-width: 100px"'.
August 22, 2012 at 9:20 am
simon, I'm not sure; i didn't see anything explicitly saying FOR XML in what you pasted; i always construct my html in three peices; header/body/tail;
because the FOR XML needs a number of replacements to cleanup XML to HTML.
in the end, after the cleanup of the body, i just concatenate together:
Select @tableHTML = @tableHeader + @tableHTML + @tableTail
here is a very loosy-goosy modification i would try:
DECLARE @tableHeader varchar(max)
DECLARE @tableTail varchar(max)
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHeader =
N'<H2>DBA Jira open Calls</H2>' +
N'<table border="1">' +
N'<tr>' +
N'<th>Issue ID</th>' +
N'<th>Summary</th>' +
N'<th>Call Date</th>' +
N'<th>Priority</th>' +
N'<th>Status</th>' +
N'<th>Assignee</th>' +
N'</tr>'
SET @tableTail = '</table>'
SELECT @tableHTML = (SELECT td = pkey,
'',
summary As
,
'',
td = convert(char(17),created,13),
'',
td = p.pname,
'',
td = iss.pname,
'',
td = assignee,
''
from jiraissue ji
--other stuff? ..........................................
For XML raw('tr'), Elements)
--these are important repacements to make valid html fromt he XML:
Set @tableHTML = Replace(@tableHTML, '_x0020_', space(1))
Set @tableHTML= Replace(@tableHTML,'_x0022_','"')
Set @tableHTML= Replace(@tableHTML,'_x003B_',';')
Set @tableHTML = Replace(@tableHTML, '_x003D_', '=')
Set @tableHTML = Replace(@tableHTML, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @tableHTML = Replace(@tableHTML, '<TRRow>0</TRRow>', '')
Select @tableHTML = @tableHeader + @tableHTML + @tableTail
--@tableHTML now ready for email
Lowell
August 22, 2012 at 9:32 am
Sorry Lowell, I use that part at the end....heres the full script...I'm still not quite clear what I need to code when using this format.....sorry for the headache!!
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H2>DBA Jira open Calls</H2>' +
N'<table border="1">' +
N'<tr>' +
N'<th>Issue ID</th>' +
N'<th>Summary</th>' +
N'<th>Call Date</th>' +
N'<th>Priority</th>' +
N'<th>Status</th>' +
N'<th>Assignee</th>' +
N'</tr>' +
cast ( (
SELECTtd = pkey,
'',
summary As
,
'',
td = convert(char(17),created,13),
'',
td = p.pname,
'',
td = iss.pname,
'',
td = assignee,
''
from jiraissueji
inner join issuestatus iss
on ji.issuestatus = iss.id
inner join priority p
on ji.priority = p.id
where ASSIGNEE in (select child_name from cwd_membership where parent_name = '[TBSINTL] IT Databases')
and issuestatus not between 5 and 6
order by p.sequence,ji.created
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients='simon.letts@xxxxxx.com',
@subject = 'Jira Calls logged to DBA Team',
@body = @tableHTML,
@body_format = 'HTML'
August 22, 2012 at 9:49 am
can you try this and tell me if it outputs what you were expecting?
DECLARE @tableHeader varchar(max)
DECLARE @tableTail varchar(max)
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHeader =
N'<H2>DBA Jira open Calls</H2>' +
N'<table border="1">' +
N'<tr>' +
N'<th>Issue ID</th>' +
N'<th>Summary</th>' +
N'<th>Call Date</th>' +
N'<th>Priority</th>' +
N'<th>Status</th>' +
N'<th>Assignee</th>' +
N'</tr>'
SET @tableTail = '</table>'
SELECT @tableHTML = (SELECT
td = pkey,
summary As
,
td = convert(char(17),created,13),
td = p.pname,
td = iss.pname,
td = assignee
from jiraissue ji
inner join issuestatus iss
on ji.issuestatus = iss.id
inner join priority p
on ji.priority = p.id
where ASSIGNEE in (select child_name from cwd_membership where parent_name = '[TBSINTL] IT Databases')
and issuestatus not between 5 and 6
order by p.sequence,ji.created
For XML raw('tr'), Elements)
--these are important repacements to make valid html from the XML:
Set @tableHTML = Replace(@tableHTML, '_x0020_', space(1))
Set @tableHTML = Replace(@tableHTML,'_x0022_','"')
Set @tableHTML = Replace(@tableHTML,'_x003B_',';')
Set @tableHTML = Replace(@tableHTML, '_x003D_', '=')
Set @tableHTML = Replace(@tableHTML, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @tableHTML = Replace(@tableHTML, '<TRRow>0</TRRow>', '')
Select @tableHTML = @tableHeader + @tableHTML + @tableTail
--@tableHTML now ready for email
PRINT @tableHTML
SELECT @tableHTML
Lowell
August 22, 2012 at 9:59 am
Hi Lowell, thanks for your help so far. Yes the code works like this, but when I add the email part to send, it's still only the width of the body page.. So other columns are now only 4 chars across, yet they have 30 characters in the field! Does that make sense? Basically what I need is something that allows all columns to fir with the data unwrapped ie like a scroll bar!
August 22, 2012 at 11:14 am
question makes sense sure;
i think the question is all about css layout;
so if you added a default to theTD class, it might do what you are asking, and is trivial to test: you could also simply assign a default widht of, some # of pixels to accomplish something,a nd allow it to wrap after it gets bigger than that.
change this line:
and see if adding NOWRAP to your page makes the difference you might be looking for?
SET @tableHeader =
N'<head><style TYPE="text/css"> td { white-space:nowrap;} </style><body>DBA Jira open Calls</H2>' +--...the reast of the header
Lowell
August 23, 2012 at 4:15 am
That is exactly it Lowell!! Thanks so much for your time on this, it works a treat and saves me time re-working other solutions 🙂
August 23, 2012 at 5:21 am
simon.letts (8/23/2012)
That is exactly it Lowell!! Thanks so much for your time on this, it works a treat and saves me time re-working other solutions 🙂
Excellent! glad this worked for you!
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply