VirtualObjectives

Saving SQL Query Results as HTML

The following stored procedure allows you to output query results to either a HTML file or text.

Rather than explain the script itself, I'll show you several examples of what it can do for you.

Download SaveTableAsHTML (Size 6 KiB)

Installation:

The script creates a stored procedure called SaveTableAsHTML in any desired database. You can place it in specific databases, otherwise, if it will be commonly used by various processes, then you can place it in the master database.

Depending on what option you choose for the parameter @DBUltra, you may need to enable the following...

If @DBUltra = 0 then it will require the use of xp_cmdshell. SQL Server 2008 R2 and above, by default, have disabled this use of xp_cmdshell call to SQLCMD.
If using temporary tables as the source then please use global temporary tables.
To enable this on the server, run the following script in a sysadmin query session on the server:

If @DBUltra = 1 then it will require the use of sp_OACreate, sp_OAMethod and sp_OADestroy which may be restricted.
If so, run the following script in a sysadmin query session on the server:

General Usage:

The SaveTableAsHTML stored procedure accepts the following parameters..

Parameter Optional/
Mandatory
Data Type Function
@DBFetch Mandatory varchar(4000) Specifies the source dataset. The parameter can be a table name (including a global temporary table if using the SQLCMD method), view name, user-defined function call or SELECT statement.
Use the three part dot notation. For example [database].[schema owner].[table]
The use of implicit functions like CAST, CONVERT etc. cannot be specified in the @DBFetch parameter.
@DBWhere Optional varchar(2000) Specifies a WHERE clause to filter the data specified by the @DBFetch parameter.
NOTE: You are required to return the column used in @DBWhere to the output results.
@DBThere Optional varchar(2000) Specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter.
@PCWrite Optional varchar(1000) @PCWrite specifies the location for the data file. The parameter must provide a complete path, including file name, to a location where the SQL Server service account is allowed to create a file.
Example: @PCWrite='C:\Temp\test.csv' will write the file test.csv in the C:\Temp\ directory of the SQL Server.
UNC paths to other servers can also be used provided the SQL Server service account has access to write the file.
If no value is provided the results are returned as text.
When you use this parameter, you will normally set the parameter @DBUltra = 1
@DBUltra Optional

Default = 1
bit @DBUltra is optional and it specifies which data-file-creation method to use.
A value of zero (0) uses XP_CMDSHELL and SQLCMD method.
A value of one (1) uses the OLE automation method. (Default. Preferred method)
@CSS Optional varchar(4000) Allows you to specify a CSS style for the HTML page and hence table.
You can specify whatever CSS you like OR you can define a predefined style by just using the name.
Predefined styles are: Orange, Blue, Purple, Green, Dark, Light
Example: @CSS = 'blue'
There is no need to specify <style type="text/css"> when using @CSS
Using this parameter will wrap the table in full <html> tags.
@TableStyle takes precedence over @CSS. See @TableStyle to specify CSS just for <table>.

The default is..
table {font: 80% Verdana, Arial, Helvetica, sans-serif; color: #000; text-align: left; border-collapse: collapse; border: 1px solid #dadada;}
tr:nth-child(odd) {background-color: #ebf3ff;}
tr:hover {background-color: #3d80df;color: #ffffff;}
th {font-size: 105%;color: #000;background: #ffffff repeat-x;height: 33px;border-left: 1px solid #dadada;}
tr {vertical-align: top;}
tr,th,td {padding: 5px;}
td {border: 1px solid #dadada;}
@TableStyle Optional varchar(1000) Allows you to specify a CSS style for the table. This is limited only to the style of the <table> tag. Hence <table style="...">
@TableStyle takes precedence over @CSS
The output contains only <table> </table> tags. This is so that you can insert the query results with other tables in a HTML page. However, if @PCWrite is used then full <html> tags are used.

@TableStyle example is...
border-width:thin; border-spacing:2px; border-style:solid; border-color:gray; border-collapse:collapse; background-color:#eff1f5; font-family:arial,helvetica,sans-serif; font-size:13px;
@Header Optional

Default = 1
bit Used to output the column names as the first row.
A value of zero (0) does not include header in the output.
A value of one (1) includes header in the first line of the output. (Default)
@DateTimeStyle Optional

Default = 120
tinyint Date Time Style for the CONVERT function. Default is 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)

It's awkward to embed single quotes in a string literal with T-SQL. For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in @DBFetch, @DBWhere and @DBThere. For example:

The SaveTableAsHTML stored procedure creates a HTML file at the @PCWrite location on the SQL Server and places in it the columns from the @DBFetch data set (optionally filtered/sorted by @DBWhere or @DBThere).

Example 1: Basic calls which output to the screen

This example exports a product list ordered by ProductNumber from the AdventureWorks2014 database.

A similar method to the example above is...

You can also specify the selection criteria in @DBFetch. @DBWhere is used to specify the WHERE clause. Since @Header=0 the output will not include the column names in the table header.

You can always use the preferred method which is to specify the entire query in the @DBFetch parameter...

SaveTableAsHTML works with temp tables, so there is no reason why you can't do the following. This is quite important because implicit functions like CONVERT may not be specified in the @DBFetch parameter...

Example 2: Sending the output to a file

Use the @PCWrite parameter to specify where on the SQL Server to output the HTML file. You could also use a UNC path.

When using @PCWrite, there are two methods available to output the file, the default is OLE automation and the other is uses SQLCMD. Please refer to the installation section above as you may need to enable OLE automation and/or xp_cmdshell.

The following example will output all customers to the HTML file specified using the SQLCMD method. It is also using the @DateTimeStyle parameter to format the date\time columns.

Example 3: Using SaveTableAsHTML in Email

The following example shows how to use SaveTableAsHTML in an email...

Example 4: Output to a File using different CSS Styles

The following example produces a fancy table formatting using the @CSS parameter.
It uses predefined CSS called "Orange"...

You can also define your own CSS...

Caveats/Recommendations:

  1. Column titles must not contain spaces. You will get an error if any column contains spaces. A suggestion is to use underscores.
  2. Avoid using column names that are T-SQL reserved words. Also avoid using ID as a column name. If you need to, then bound the name with braces [ID]
  3. Do not use complex queries in @DBFetch, instead, output your query to a temporary table and then reference the temporary table in @DBFetch.
  4. Sometimes SaveTableAsHTML can't handle some data types so CONVERT or CAST the results to a varchar.
  5. @DBUltra = 0 (BCP) will not work on SQL Server 2005 due to bug with BCP, however, it will work on SQL Server 2008 R2 and above.
  6. If you are using @DBUltra = 0 and a temporary table, make sure it is a global temporary table.
  7. When using the @PCWrite parameter, please make sure that the path used actually exists as the path (folder/directory) will not be created if incorrect. The file is saved on the SQL Server so the path and file needs to be appropriate. I recommend setting aside a path like C:\TEMP\ where the old files can be regularly removed.
  8. When you use the parameter @PCWrite and you get the error "Windows error [1]", then check that you specify the full path and filename of the output file and try changing the parameter @DBUltra.
  9. When using @DBWhere, you are required to return the column used in @DBWhere to the output results.

Acknowledgment:

The SQL script was originally provided by Brian Walker (http://searchsqlserver.techtarget.com)
Thanks Brian, you saved me from many many hours of frustration and stress.