SQL CLR Export Functions
This CLR Project was created as a Proof of Concept. On http://www.SQLServerCentral.com/Forums/
, you will see literally thousands of posts where people ask how to send a table or the results of a query, to disk in various formats.
The typical response is telling people to do one of following things
- Set results to grid, then Save Results As. Options are csv or tab delimited.
- use either bcp or xp_cmdshell, or a combination of the two to get the results to disk.
- use CLR (but no concrete examples are typically provided)
- Do the work outside of SQL Server completely, in a programming language for example.
If you are mentally stuck in doing the work in TSQL, the issue with that is in any environment that is slightly concerned about security, xp_cmdshell, or access to bcp on the server itself, is often not an option.
From a TSQL perspective, you are limited to formats you can create via raw text. XML, Comma Delimited, Tab Delimited, Custom Delimited and HTML are classic examples, and are included in this project.
Personally, I think the best option is to create an aspx web page that can serve up the results in any desired format; .NET is much more flexible, and a lot easier to do the more desirable formats like PDF, XLS, RTF, Word, even MHT, in addition to the text formats mentioned above. This project is actualy an adapted subset of a suite of .NET functions I created to Export Data To various file types.
However, even though an aspx page is more desirable and a better option, I created this CLR project to make exporting to those text files easier, and to show there are better options for creating those reports than opening up xp_cmdshell on your servers.
What This Project Does
- Provides 6 different Export Methods
- Export to HTML (Hyper Text Markup Language...a web page
- Export to CSV (Comma Seperated Values)
- Export to TSV (Tab Seperated Values)
- Export to Custom Delimiters (both row delim and field delim Values)
- Export to Fixed Width Format:
- Export to XML (Leverages the built in .Net DataTable.WriteXML method)
I tried to create this project with a mentor mentality. Lots of comments in the code, generous documentation, lots of comments in the generated procedures to make it understandable to beginners.
Veteran programmers may identify easier, more streamlined ways to do things, and may roll their eyes at "hungarian notation" for things like the procedure names, like CLR_ExportTableToHTML
The code is not optimized. Each procedure is a copy/paste/adaptation of the code idea...they all could instead call a central private function instead. My idea here was to have each proc as a readable, almost stand alone code snippet to make it easier to read and understand.
Centralizing the code is what i've done for myself, but that core function seems to me to be harder for a new person to read, since it takes so many different factors into consideration.
So, if it bothers you, download the project and change it! Offer suggestions!
I'll always be open to better ways to do things, and will incorporate any suggestions I receive and understand!
Thanks for looking, and I hope this helps folks in the future!Source Code is written in VB.NET . The Assemblies in the Main package come form VB.Net.