There are a lot of uses for this technique. Here is an example from Nigel Rivett for doing FTP in a stored procedure http://www.nigelrivett.net/FTP/s_ftp_GetFile.htmlhe also has a clever way of writing format files for BCP in the same way. Naturally, one wouldn't give the normal application user any access to this!.

I have to agree with Phil. "New" is kind of misleading since any DBAs worth their paycheck knew about using xp_cmdshell about 15 minutes after it was introduced back around SQL 4.21 .

Given the very narrow constraint of reserving this file creation to the handful of people blessed with membership in the sysadmins role, used for specialty troubleshooting sessions and subsequent mandatory file cleanup I'd say the article's suggestion has merit. In my environments that contain HIPAA data, this would be enabled only after that Ghost Whisperer lady put me at peace by shoving me into "the light" because saying "over my dead body" wouldn't be enough.

Also, it's pretty shallow-minded to overlook that fact that while one execution of this command may seem harmless and convenient, I look at it from the fact that I have thousands of concurrent connections 7x24. Simply "appending to a file" can run my server out of space in a heartbeat if poor choices are made. In my humble opinion, [absolutely nothing] should consume space on my server's hard drives that I don't have control over.

Sound harsh? I'm the "nice guy." My security counterparts would have a field day. Shashank would get hit harder than a bag of jellybeans at a daycare.

There is nothing inherently wrong with doing file handling from within a T-SQL script when needed , and it is possible to set the security on the windows system and on the SQL Server in such a way that it would be just as secure as using .NET code. I have in fact used precisely the technique this article describes to create schema.ini files on an as needed basis in conjunction with using opendatasource to read text files on a non-production system.

While there are certain situations where this is a good technique though, it will not often be the best technique. Depending on the amount and type of information being processed there will very often be a better answer. If it is information the user will need immediately, it is probably better to return it immediately through whatever interface is used. If it is say a report meant to be picked up and read the script could be sent to e-mail it instead of leaving it in a file. If it is a large amount of information then something along the lines of DTS, SSIS, or BCP would be more appropriate.

Samuel Clough (4/22/2008)I'm surprised by this article. For one thing, as already stated, this stored proc should never be enabled. Secondly, why use a database to do file i/o? I wouldn't want someone writing files on my database server. It sorta smells like a hack.

To me ...

I hate infinitives.

Security is a separate issue that always must be dealt with, but there is nothing at all unsecure about using xp_cmdshell in your code.

The article is not in depth enough to make a judgement regarding whether or not using xp_cmdshell was the best method. However when weighing the pros and cons, one must always consider the maintainability of the code. It doesn't get much more maintainable than a stored procedure.

There are times when it is necessary to use xp_cmdshell, but this isn't one of them. As stated in a post above the real issue is securing credentials in the web config files. Using hashing functions is one approach that works there. Other approaches use the built in security of ASP.NET. Several good articles are: