I would like to discuss an interesting way of file handling through SQL Server that I implemented in one of my projects. The problem I was facing was that ASP.NET was not allowing file write operations under its default account (ASPNET account). I was not interested in impersonating this through the web.config file as you've to write the username and password in plain text . Also the runtime impersonation using the WIN32 API is not as easy as it sounds.

Now to write contents to a file through SQL Server, you can use an extended stored procedure called master..xp_cmdshell. However, you can rest assured that good database administrators would definitely have disabled the execution of this stored procedure. So the trick works only if the execution permissions for this stored procedures are granted for the application.

This stored procedure actually executes anything you provide to it as an argument on the command prompt of the machine on which the SQL Server is installed. The execution is the same as if you had a Command Prompt window opened and typed a command.

Now as we know that if we redirect a string(call it st1) to any other arbitrary name string (call st2) , then st2 is created as a file on the machine and st1 is written in that, its syntax will be like this.

echo "your string" >> filename.txt,

This command on the command prompt will write(actually append) your string in filename.txt, If you use '>' in place of '>>' the file contents will be overwritten. In either case a new file will be created if it doesn't already exist.

Now with this info, all you've to do is to execute something like this on the SQL server

exec master..xp_cmdshell 'echo ''your string'' >> filename.txt'

and your file will be written with your message

Trust me, its performance is awesome as you need not create any File object and then call its functions to get the work done. This work is done through IO redirection, which is the fastest way to write a file.

The only disadvantage, and it is a serious one, here is that this stored procedure is infamous for being used as an SQL injection attack. Granting permission for its execution might require that you be extra careful while writing your queries as a small mistake can ensue havoc on the database server. This is because any executable can be run on the machine hosting the SQL server with xp_cmdshell .

Mine was a small application hence i thought to do the work a bit differently, you can also try this if nothing works your way .