September 2, 2014 at 12:17 pm
This is great I have something similar. What if in your type you wanted to make the cell red if it was a certain criteria is there a way to do that?
April 27, 2015 at 4:05 am
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?
In essence to combine these:
'font/@color' = case...(set up to return color # here), td = colname, ''
'td/@bgcolor' = case...(set up to return color # here), td = colname, ''
I've tried a few variations, but with not much success.
Regards, Greg.
August 25, 2015 at 7:05 am
i m trying this code after providing profilename:the mail is gone in queued status.mail can not send
August 25, 2015 at 7:07 am
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
SET @subject = 'XBRL Report'
SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd){ background-color:#eee; }
tr:nth-child(even){ background-color:#fff; }
</style>'+
N'<H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green"><th>Version</th>
<th>Total</th>
<th>Processed</th>
<th>UnProcessed</th>
</tr>' +
CAST ( (
SELECT td = CAST([Requested_By] AS VARCHAR(100)),'',
td = [Requested_By],'',
td =[Total_Count],'',
td = [UnProcessed_Count] ,'',
td = [Processed_Count]
FROM@TEMP_XBRL_DETAILS
FORXML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
EXEC msdb.dbo.sp_send_dbmail @recipients='deshmukhp12384@gmail.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name ='XBRL_Admin ';
print @tableHTML;
this gives me result:
(1 row(s) affected)
Mail (Id: 15) queued.
<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd){ background-color:#eee; }
tr:nth-child(even){ background-color:#fff; }
</style><H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3><table id="box-table" ><tr><font color="Green"><th>Version</th>
<th>Total</th>
<th>Processed</th>
<th>UnProcessed</th>
</tr><tr><td>Web</td><td>Web</td><td>432</td><td>0</td><td>432</td></tr></table>
Kindly help me do done it sucessfully
August 25, 2015 at 9:26 am
greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?In essence to combine these:
'font/@color' = case...(set up to return color # here), td = colname, ''
'td/@bgcolor' = case...(set up to return color # here), td = colname, ''
I've tried a few variations, but with not much success.
Regards, Greg.
I know this post is a bit old but here's a section of the code that I use to create one of my morning reports that does these types of things. Using CSS would probably be the better way but I didn't know how to even spell CSS when I first did this.
--===== Create the starting tags, table definition, title caption for the table, and column headers.
SELECT @Body =
'
<html>
<Body>
<table border="1" cellpadding = "4" cellspacing="0" style="font-family:arial; color:black; font-size:10pt;">
<caption style=" border:1; background-color:White; font-weight:bold; font-size:14pt;">
Morning Report - 24 Hour Job Summary for ' + @@SERVERNAME + '
~ ' + CONVERT(CHAR(17),GETDATE(),113) + ' ~
</caption>
<tr style="background-color:Blue; color:White; ">
<th>Job Name</th>
<th>Is Enabled</th>
<th>Is Scheduled</th>
<th>Next Run Date</th>
<th>Min Run Date</th>
<th>Max Run Date</th>
<th>Max Duration</th>
<th>Total Runs</th>
<th>Passed</th>
<th>Failed</th>
<th>Retried</th>
<th>Cancelled</th>
<th>Last Modified</th>
</tr>
--'
+ REPLACE(REPLACE(CAST(
( --=== This formats each column as HTML. CASE statements provide changes in background color.
SELECT
---------------------------------------------------------------------------------------------------------------
= 'left', td = JobName,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN IsEnabled = 'Yes' THEN 'background-color:White;' ELSE 'background-color:Yellow;' END,
= 'center', td = IsEnabled,'',
---------------------------------------------------------------------------------------------------------------
= CASE
WHEN IsScheduled ='Yes' THEN 'background-color:White;'
WHEN IsScheduled ='One Time' THEN 'background-color:DarkOrange;'
ELSE 'background-color:Yellow;'
END,
= 'center', td = IsScheduled,'',
---------------------------------------------------------------------------------------------------------------
= CASE
WHEN NextRunDate = '-' THEN 'background-color:White;' -- Rev 03
WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-30,GETDATE())
THEN 'background-color:Red;'
WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-7,GETDATE())
THEN 'background-color:Yellow;'
ELSE 'background-color:White;'
END,
= 'center', td = NextRunDate,'',
---------------------------------------------------------------------------------------------------------------
= 'center', td = MinRunDate,'',
---------------------------------------------------------------------------------------------------------------
= 'center', td = MaxRunDate,'',
---------------------------------------------------------------------------------------------------------------
= 'center', td = MaxDuration,'',
---------------------------------------------------------------------------------------------------------------
= CASE
WHEN IsScheduled = 'Yes' AND TotalRuns = '0'
THEN 'background-color:Yellow;'
ELSE 'background-color:White;'
END,
= 'right', td = TotalRuns,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Passed = TotalRuns THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Passed,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Failed IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Failed,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Retried IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Retried,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Cancelled IN ('0','-')THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Cancelled,'',
---------------------------------------------------------------------------------------------------------------
= 'center', td = LastModified,''
FROM cteAssembleReport
ORDER BY JobName
FOR XML PATH('tr'), TYPE --This concatenates everything nicely in <tr></tr> tags for each row in the final table.
)
--===== This formats the HTML for human readability for troubleshooting purposes. (End of CAST and REPLACE's)
AS VARCHAR(MAX)), '</tr>','</tr>'+CHAR(13)+CHAR(10)), '<tr>',SPACE(12)+'<tr>')
+ --=== This finalizes the starting tags.
' </table>
</body>
</html>
'
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2015 at 2:21 pm
I usually first create an HTML document using some WYSIWYG (what you see is what you get) tool apply all the formatting I want through the interface and then look at the underlying HTML. Any version of Visual Studio works, you can even save Word document as .htm and see it in any text editor. That's how I've learned html in the first place.
August 25, 2015 at 5:00 pm
Jeff Moden (8/25/2015)
greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?In essence to combine these:
'font/@color' = case...(set up to return color # here), td = colname, ''
'td/@bgcolor' = case...(set up to return color # here), td = colname, ''
I've tried a few variations, but with not much success.
Regards, Greg.
I know this post is a bit old but here's a section of the code that I use to create one of my morning reports that does these types of things. Using CSS would probably be the better way but I didn't know how to even spell CSS when I first did this.
Actually Jeff, for html email, inline styles work best across most email clients, so your lack of spelling ability will have been a benefit this time 🙂
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 25, 2015 at 5:03 pm
mister.magoo (8/25/2015)
Jeff Moden (8/25/2015)
greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?In essence to combine these:
'font/@color' = case...(set up to return color # here), td = colname, ''
'td/@bgcolor' = case...(set up to return color # here), td = colname, ''
I've tried a few variations, but with not much success.
Regards, Greg.
I know this post is a bit old but here's a section of the code that I use to create one of my morning reports that does these types of things. Using CSS would probably be the better way but I didn't know how to even spell CSS when I first did this.
Actually Jeff, for html email, inline styles work best across most email clients, so your lack of spelling ability will have been a benefit this time 🙂
Excellent. That's good to know because when it comes to CSS, I have a major case of CRS. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2015 at 6:48 pm
Interesting how many different ways there are to do this.
Back in the early SQL 2000 days I wanted to send HTML-formatted reports from SQL Server; DB_SENDMAIL was the first option I considered, and was very quickly rejected, too much hassle with profiles (our senior SysOp had some very picturesque things to say about having windows-recognisable email profiles on Windows 2000 Server systems that were expected to be reliable). At one point we had xp_commandshell calling cscript to execute a JS script to send email, but that was superseded by a queuing approach, because in the early days (before SQL 2000 SP2 if I remember correctly) XP_CMDSHELL was kind of insecure (I think the proxy mechanism was broken).
There were two sorts of report to be sent: things that were initiated from within SQL part of the system (problem reports) and things that were requested from outside (status reports, summaries, and so on). The SQL initiated things were all pretty short, and could be dumped into queue (a table) that would be serviced by an aynchronous task (scheduled by SQL Agent) with columns for to, cc, bcc, from, subject, priority and body fields; and the things initiated from ouside could mostly be made to be jobs run by SQL Agent (either scheduled for regular reports and summaries or by manually starting the job - using a privileged sp that ony started that job- for on demand things). Generally the jobs were ACTIVESCRIPTING jobsteps (often called ACTIVE-X jobsteps or JavaScript jobsteps) that called a stored procedure to get the formatted data and mailing parameters (and in the case of the queue servicer the priority) and then created a an email object using them and called its send method. so no profiles required. The synchronous stuff (but not the stuff passed through the queue) could potentially have longer bodies because multiple rows could be returned by an SP, but I don't think that was ever exploited. If some sql detected a problem that warranted instant email it could park the message in the queue and start the job to service the queue (if it wasn't already running because of its schedule).
Tom
August 27, 2015 at 7:03 am
The very first comment I received after posting this article was from Jeff Moden ("RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row") saying basically, "Why don't you just use Database Mail?" Per usual, Jeff is right. There are three steps to the article, but it should really be just two - create the proc for the HTML and then a job that executes the proc. Below is how to do that. I will add an edit to the article.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MYSERVER Email',
@recipients = 'foo@foo.com',
@subject = 'The Email Subject',
@query =N'EXECUTE MYDB.dbo.procHtmlSql',
@attach_query_result_as_file = 0,
@body_format = 'HTML';
Steve
August 27, 2015 at 7:36 am
TomThomson (8/25/2015)
Interesting how many different ways there are to do this.Back in the early SQL 2000 days I wanted to send HTML-formatted reports from SQL Server; DB_SENDMAIL was the first option I considered, and was very quickly rejected, too much hassle with profiles (our senior SysOp had some very picturesque things to say about having windows-recognisable email profiles on Windows 2000 Server systems that were expected to be reliable). At one point we had xp_commandshell calling cscript to execute a JS script to send email, but that was superseded by a queuing approach, because in the early days (before SQL 2000 SP2 if I remember correctly) XP_CMDSHELL was kind of insecure (I think the proxy mechanism was broken).
I have email setup on all the servers that need it but, to be honest, I don't usually use it for any of the DBA reports that I generate nor any of the emails that I may have to send due to the ad hoc request. Although it has changed over time and allows a bit more flexibility than it used to (especially in the area of the FROM address), old habits are hard to break (because they do work well in this case) and I still use CDOSYS routines (saved in my UTIL database, of course) to send emails. It does such nice things as automatically filling in the current server name @NoReply if there's no FROM address and filling in the Subject line with another server oriented message if there's no Subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2015 at 5:58 am
Has anyone ever done the reverse operation? I.e. a table from a web page saved to an html file loaded into a SQL Server table.
August 28, 2015 at 6:28 am
Michael Meierruth (8/28/2015)
Has anyone ever done the reverse operation? I.e. a table from a web page saved to an html file loaded into a SQL Server table.
I user a CLR to to call web services or download the html of a web page, so that it can be parsed or scraped for the data in the page itself.
so the CLR is the easy part, it's parsing the varchar(max) results to extract the relevant pieces you want that costs some time. every page is kind of different; you could iterate through each table/tr/td pretty easily, but a lot of sites are switching over to nested divs, that look the same as a table, but you have to parse them differently.
//requires
//Imports System.Net
[Microsoft.SqlServer.Server.SqlFunction()]
public static SqlChars CLR_WebQuery(string URL)
{
// SqlString = varchar(8000) , SqlChars=varchar(max)
WebRequest request = HttpWebRequest.Create(URL);
using (WebResponse response = request.GetResponse()) {
using (Stream dataStream = response.GetResponseStream()) {
using (StreamReader reader = new StreamReader(dataStream)) {
string responseFromServer = reader.ReadToEnd();
return new SqlChars(new SqlString(responseFromServer));
}
}
}
request = null;
}
VB.Net Version:
'requires
'Imports System.Net
<Microsoft.SqlServer.Server.SqlFunction()>
Public Shared Function CLR_WebQuery(ByVal URL As String) As SqlChars ' SqlString = varchar(8000) , SqlChars=varchar(max)
Dim request As WebRequest = HttpWebRequest.Create(URL)
Using response As WebResponse = request.GetResponse()
Using dataStream As Stream = response.GetResponseStream()
Using reader As New StreamReader(dataStream)
Dim responseFromServer As String = reader.ReadToEnd()
Return New SqlChars(New SqlString(responseFromServer))
End Using
End Using
End Using
request = Nothing
End Function
Lowell
August 28, 2015 at 6:29 am
I haven't but this is a cool browser plug-in that you might be able to use: https://www.kimonolabs.com
It wouldn't be saving the html file but in theory you can access the data in the web page table via the API then insert the data in MSSQL. Probably would need some sort of .NET app to do it though, or CLR.
August 28, 2015 at 8:16 am
One way we use something like this is to send alerts. A scheduled job checks for problems, and emails a formatted html report to a list of recipients with sp_send_dbmail. We then text the recipients, just saying 'please check your email', by emailing to something like 1231231234@txt.att.net. No need for third-party messaging tools/subscriptions.
Viewing 15 posts - 46 through 60 (of 82 total)
You must be logged in to reply to this topic. Login to reply