Tuesday, 29 April 2014

Handy HTA to format SQL results as Table for email

Have you ever tried to email the results of a SQL query in SSMS and found that the format is a mess, just tab delimited text?  I've just searched for neat way to get the results to show as a table in a new email, and it seems that plenty of people have resorted to messing about in Programmability to wrangle SQL into creating HTML formatted text.

However, I remember from my experiments with PHP that if you cut-n-paste from a table on a webpage, the table format is preserved onto the email. I decided to play with a new HTA to show the clipboard contents....

... convert to HTML and show that in a new webpage (another HTA just for simple display purposes). From there, with any CSS tarting up you like, it can then be copied into an email, looking most spiffing. Shame that the tr:nth-child(odd/even) CSS doesn't work in HTA/MSIE for alternating row colours!

 The HTA below gives results like this :

 I hope you find this useful, either functionally or for educational purposes :)


  1. Ctrl-C your SSMS SQL query results
  2. Double-click the HTA file/shortcut
  3. Observe the clipboard data is correct, press the button
  4. Marvel at the new HTA showing your data looking more presentable
  5. Cut-n-paste to your email. Optional step: Allow yourself a smile.

With thanks to Notepad++ and the NppExport.."Copy HTML to clipboard" plugin for the syntax colouring, here is my HTA file :  Clip Table.hta

<head> <title>My SQL Table pretty-upper</title> <HTA:APPLICATION ID="oHTA" APPLICATIONNAME="HTA Test" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="maximize" > </head> <SCRIPT LANGUAGE="javascript"> function NoContextMenu(){ alert("Sorry, but right-click is not allowed."); }; function replaceAll(find, replace, str) { return str.replace(new RegExp(find, 'g'), replace); }; function Window_onLoad(){ // Use the following line to disable right-click menu and prevent View Source document.oncontextmenu = new Function ( "NoContextMenu(); return false" ); // Resize and center window.resizeTo(screen.availWidth*.75, screen.availHeight*.75); window.moveTo(screen.availWidth*0.125, screen.availHeight*0.125); // Read Clipboard, convert tabs and newlines to HTML if (window.clipboardData) // Internet Explorer { txt_str1.value = window.clipboardData.getData("Text"); contents = window.clipboardData.getData("Text"); contents = replaceAll("\t","</TD><TD>",contents); // Tabs : table column boundaries contents = replaceAll("\r\n","</TD></TR>\r\n<TR><TD>",contents); // CrLf : new table row contents = "<TR><TD>" +contents +"</TD></TR></TABLE></HTML>"; // wrap up, except for TABLE/CSS }; }; function fn_run_button(){ txt_str1.value = contents; // Write a new smarter table in new HTA in %TEMP% folder var oWSS = new ActiveXObject("wscript.shell"); hta_file = oWSS.ExpandEnvironmentStrings("%TEMP%") + "\\testhta.hta"; // alert(hta_file); // line by line alternative : var a = escaped.split("\r\n"); for (var i = 0; i < oFil.length; i++) { *use* a[i] } // Write to file ( \x22 in the CSS is escape code for " ) var oFSO = new ActiveXObject("Scripting.FileSystemObject"); var oFil = oFSO.CreateTextFile(hta_file, true); oFil.WriteLine("<HTML><style type=\x22text/css\x22>"); oFil.WriteLine("#box-table { font-family: \x22Lucida Sans Unicode\x22, \x22Lucida Grande\x22, Sans-Serif;"); oFil.WriteLine(" font-size: 12px; text-align: left; border-collapse: collapse; border-top: 3px solid #9baff1; border-bottom: 3px solid #9baff1; }"); oFil.WriteLine("#box-table th { font-size: 13px; font-weight: normal; background: #b9c9fe; border-right: 2px solid #9baff1; "); oFil.WriteLine("border-left: 2px solid #9baff1; border-bottom: 2px solid #9baff1; color: #039; }"); oFil.WriteLine("#box-table td { border-right: 1px solid #aabcfe; border-left: 1px solid #aabcfe; border-bottom: 1px solid #aabcfe; color: #669; }"); oFil.WriteLine("</style> <table id=\x22box-table\x22 >"); oFil.WriteLine(contents); oFil.Close(); alert( "continue...") oWSS.run("cmd /C start " +hta_file, 0,false); oWSS = null; window.close(); }; window.onload=Window_onLoad; </SCRIPT> <BODY bgcolor="#90BBEE" > <font face="Lucida Sans"> <CENTER> <h1>SQL Tables made better for emailing</h1> This creates a new HTA from the clipboard data, which looks better and can be pasted into an email. <P><P> Clipboard <BR> <textarea name="txt_str1" cols="120" rows="40" ></textarea> <P> <input type="button" value="Create smarter table" name="run_button" onClick="fn_run_button()"> </CENTER> </BODY>