Saving SQL Query Results as HTML

The following stored procedure allows you to output query results to either a HTML file or text without the need to use SSIS, DTS packages or BCP. This stored procedure can be used to replace sp_makewebtask which is disabled by default in SQL Server 2005.

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

SaveTableAsHTML (Size 9 KB)

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 2005, by default, has disabled this use of xp_cmdshell. To enable this on the server, run the following script in a Query Analyzer sysadmin session on the server:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

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 Query Analyzer sysadmin session on the server:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures',1;
RECONFIGURE;

General Usage:

The SaveTableAsHTML stored procedure accepts 7 parameters, but only @DBFetch is required.

Parameter	Optional/ Mandatory	Data Type	Function
@DBFetch	Mandatory	varchar(4000)	Specifies the SELECT statement used to return a result set from a table (including a temporary table if using the OSQL method), view name, or user-defined function call.
@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. If no value is provided the results are returned as text in the form of HTML table only, without <html> and <body> tags. When you use this parameter, you will normally set the parameter @DBUltra = 1
@DBWhere	Optional	varchar(2000)	Specifies a WHERE clause to filter the data specified by the @DBFetch parameter.
@DBThere	Optional	varchar(2000)	Specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter.
@DBUltra	Optional	bit	@DBUltra is optional and it specifies which data-file-creation method to use. A value of zero (0) uses the OSQL method. A value of one (1) uses the OLE automation method. (Default)
@TableStyle	Optional	varchar(1000)	Allows you to specify a CSS style for the table. The default value for @TableSyle is... border-width: thin; border-spacing: 2px; border-style: solid; border-color: gray; border-collapse: collapse;
@Header	Optional	bit	Used to output the column names as the first line. 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)

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:

EXEC master..SaveTableAsHTML @DBFetch='select * from Northwind..Customers', @DBWhere='Country = ^Italy^'

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

The following examples do not use the parameter @PCWrite which will output the results as text to Query Analyzer. The HTML code is contained by the <TABLE> </TABLE> tags only. This is so that you can insert the query results with other tables in a page.

This example exports a product list sorted/ordered by ProductID from the Northwind database.

USE Northwind
EXECUTE SaveTableAsHTML NULL, 'Products', NULL, 'ProductID'

A similar method to the example above is..

USE Northwind
EXECUTE SaveTableAsHTML @DBFetch = 'Products', @DBThere = 'ProductID'

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.

USE Northwind
EXECUTE SaveTableAsHTML @DBFetch = 'select CompanyName, ContactName, Country from Suppliers', 
@DBWhere = 'Country = ^France^', @Header = 0

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

USE Northwind
EXECUTE SaveTableAsHTML @DBFetch = 'select CompanyName, ContactName, Country from Suppliers 
where Country = ^France^', @Header = 0

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.

USE Northwind
select CompanyName, ContactName, Country into #tmp from Suppliers where Country = 'France'
EXECUTE SaveTableAsHTML @DBFetch = #tmp, @Header = 0

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. The HTML file is fully bound by <HTML> </HTML> tags.

When using @PCWrite, there are two methods available to output the file, the default is OLE automation and the other is uses OSQL. 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 OSQL method.

EXEC master..SaveTableAsHTML @DBFetch = 'select * from Northwind..Customers', 
@PCWrite='c:\temp\test.htm', @DBUltra = 0

Example 3: Using SaveTableAsHTML in Email

The following example shows how to use SaveTableAsHTML in an email. This example is suited only to SQL 2005 as sp_send_dbmail is used.

EXECUTE msdb..sp_send_dbmail
@recipients = 'someone@domain.com',
@subject = 'Test',
@body_format = 'html',
@exclude_query_output = 1,
@append_query_error = 1,
@query = ' PRINT ''<h2>Addresses</h2>''
EXECUTE SaveTableAsHTML @DBFetch = ''select top 10 * from AdventureWorks.Person.Address where City = ^Sydney^''
PRINT ''<br>''
PRINT ''<h2>Products</h2>''
EXECUTE SaveTableAsHTML @DBFetch = ''select top 10 * from AdventureWorks.Production.Product'' '

SQL Formatting Errors:

Occasionally, the query you are using and/or the column data type or name will produce an error similar to the one below:

Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 269 A cursor with the name 'Records' does not exist. Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 272 A cursor with the name 'Records' does not exist. Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 321 A cursor with the name 'Records' does not exist. Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 321 A cursor with the name 'Records' does not exist.

Usually this error is a result of the stored procedure SaveTableAsHTML not being able to interpret the results so it can convert the data to your requirements. Please check the query you are using in @DBFetch and refer to the Caveats/Recommendations section below for more information.

If you are using the OSQL method (where @DBUltra = 0) you may see an error when outputting records from tables with large numbers of columns. This is because SaveTableAsHTML has limited OSQL queries to 8000 characters. We suggest either using the default OLE automation method (where @DBUltra = 1) or limiting the number of columns returned.

Caveats/Recommendations:

Column titles must not contain spaces. You will get an error if any column contains spaces. A suggestion is to use underscores.
Do not use complex queries in @DBFetch, instead, output your query to a temporary table and then reference the temporary table in @DBFetch.
Sometimes SaveTableAsHTML can't handle some data types so CONVERT or CAST the results to a varchar.
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.
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 setting the parameter @DBUltra = 0.

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.