Write Reports from SQL to Disk (Even HTML!)

Total article views: 3094 | Views in the last 30 days: 95

 Rate this |   Join the discussion |  Briefcase

Ever need to write reports out to a folder? I have found that creating output files are SA-WEET! (and easy too) The sample script uses BCP to create an HTML file.

This process works well for reports that need to be generated nightly and take too long to run in real time.  Use an SMTP mail agent, and ALAS web based mail!


First open the script and change MYDATABASE to your database.

Second, add the script to your database then run the following:
  --Create the HTML file
  usp_writeSysObjectReport 'C:\sysobjects.html'
  --See the contents
  master..xp_cmdshell 'type c:\sysobjects.html'

  --Run this to clean up (uncomment)
  --master..xp_cmdshell 'erase c:\sysobjects.html'

Summary:
You can use BCP to create HTML documents and/or HTML mail. Can create many reports fast! You could modify the output to push it to a web server.

Total article views: 3094 | Views in the last 30 days: 95