I suspect that many database developers use other applications to extend the use of the SQL Server system. I like using Windows Scripting resources such as the Scripting.FileSystemObject COM objects within stored procedures, and find such mundane command-line tools as Type, FTP and Xcopy of great use, when called via XP_cmdshell. I would like, of course to warn anyone new to SQL Server to be very cautious indeed of using things like this in a production system, and be sure to have adequate reporting, and some conservative error checking in place.

There are other, third party tools, I like using too, and I’d be fascinated to hear from anyone else who a favourite tool like these, or who disagrees that such things should ever be used in a database. They are mostly simple to use and are generally controllable via OLE Automation or XP_cmdshell. These are my favourites.

cURL groks URLs

A wonderful tool that can be thought-of as TYPE on steroids. On another BLOG, I give an illustration of its use for grabbing a CSV file from a website, but it will do HTTP and FTP, and will work over SSL. Spoofing cookies and userAgents is no problem for it. If the file is out there, it will get it.
More about cURL…

Persits ASPEmail.

A mature product that works very well using the OLE-interface. It is extremely useful as an email-sending tool, as it has advanced features not found in the standard system, such as queueing, alternative Text content for HTML emails, and embedded graphics. For HTML-based emails it works very well. I find it very reassuring to get a nicely-formatted one-page daily-summary of the production databases I look after, and it makes management-reporting a lot slicker. It makes the automated dispatch of the excruciatingly-named ‘Ezines’ a doddle.
More information here…

EMS Data Export

This utility will write a the results of a SQL expression out to a Spreadsheet, or a variety of other formats such as MS Excel, MS Access, MS Word, RTF, HTML, PDF, XML, TXT, DBF, CSV, SYLK, DIF, LaTeXs Clipboard. I find that users of database systems like receiving emailed reports as attachments in Excel format, with correct formatting and so on. With this tool, it is possible to do so, and I can recommend it if you are prepared to devote the time to get it to work smoothly, but the version that I have is a bit quirky and, for a while, the licensing system couldn’t be persuaded that SQL Server had a valid license to use the application. The good folks at EMS are very responsive, and did me a version that worked properly in command-line mode when called from xp_cmdshell, but I wouldn’t necessarily recommend the tool for the faint-hearted. However, once it bursts into life, it does a good solid job with financial reports, and the Beanies love it.
EMS Data Export for SQL Server can be found here…

BerSoft HTMLprint

When anyone mentions the idea of printing things out from SQL Server, I dive for cover. The idea is horrible. However, my happy relationship with HTMLprint happened when the boss insisted that a SQL Server accounting system I’d written should be able to print out special invoices with addresses that could be folded, origami-like to fit inside stick-on labels for shipping goods. It had to be accurate. And whilst I was about it, he wanted a watermarked copy for the files and copies printed out in accounts. HTMLPrint came to the rescue. It prints out HTML files, as simple as that, to any printer on the network. The writers adopted the painful approach of writing the renderer from scratch rather than using IE or Mozilla. To their credit it works, as long as you are careful (my version does not recognise inline style blocks).
Unlike the other utilities in this list, this can be set up to be used merely by writing HTML files to a particular directory. Whilst this is convenient, the only way to get parameters to HTMLprint is via an INI file, so I always set the thing up as required and leave it be.
You can find out more here…

Aurelia Reporter.

Reporter is one of those tools that can provide a very quick solution to a problem that would normally create a considerable headache. I was once faced with the challenge of indexing a vast mass of Word documents, Excel spreadsheets, and so on. ‘Resources’ as the customer called them, into an intranet system with a google-like interface so they could find stuff easily, view it via a browser, and categorise it. The customer told me that they’d been informed that the task was impossible. Now, of course, there are a host of document management systems eager to do the job if you are prepared to open the walle pretty wide.
This utility made it all rather trivial. It is a printer driver that, instead of printing to a printer, renders the print to HTML, using CSS to precisely position text. It is astonishingly versatile and will even send the results automatically as an email with embedded fonts and graphics. With a command-line batch-mode utility, one can render a vast polyglot mass of documents and materials, created by any application that can print, into pretty good HTML documents, using text where possible. Once you have a system up and running for sniffing out what has changed and what has been added, and converting those into HTML, the task is reduced to a database indexing system. You can even use SQL Server’s own text searching system.
I’ve also used it for reading text from a mass of .WPS files, importing data from the Works Spreadsheet, and a host of other tricky conversion jobs.
Find out more about Reporter here…

So what are your favourite utilities? Or what are your reasons for never calling utilities from within stored procedures?