Export SQL queries as HTML/Excel in SQL Server 2005

1 minute read

In SQL Server 2000 it was quite easy to export a query as HTML via the Web Assistant Wizard. This nice little feature allowed an easy way of quickly generating an export without the need to setup a DTS package.

In SQL Server 2005 the Web Assistant Wizard has been replaced with more the integrated Reporting Services. Either way if your looking to quickly export data to show to management or a client then you’ll be pleased to know that the Web Assistant stored procedures still remain. There are three system stored procs:

The one that we’ll be using is the sp_makewebtask proc.

The SP accepts loads of parameters, but to achieve the basics all we need to use are a few. In the TSQL below, I execute the makewebtask and pass in a path to the output file and the query. Be sure to reference the table via it’s full name as shown below. The best thing about this is that we can change the extension from .htm to .xlsx and bring it into Excel, this is because Excel can render html. Another thing to note about the code below is that by default SQL Server 2005 disables the Web Assistant Procedures due to it seeing them as a potential security risk. So I enable them first with sp_configure and then reset them back to disabled again once the file has been exported.

   1: sp_configure 'Web Assistant Procedures', 1
   2: go
   3: reconfigure
   4: go
   5: exec sp_makewebtask @outputfile = 'c:\test.htm',@query = 'select * from DatabaseName.dbo.DatabaseTable'
   6: go
   7: sp_configure 'Web Assistant Procedures', 0
   8: go
   9: reconfigure
  10: go

Also make sure you have enabled advanced options by running the following:

   1: sp_configure 'show advanced options', 1
   2: GO
   3: reconfigure

The SP’s were originally used to create jobs in SQL Server (filed under the job category of “Web Assistant”) so there’s loads of options like scheduling etc. that’s really useful, so I recommend you view the documentation.