Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

File Handling A Different Way

By Shashank Bhide,

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 .

Total article views: 7898 | Views in the last 30 days: 1
 
Related Articles
FORUM

executing oracle procedure from sql server

executing oracle procedure from sql server

FORUM

Execute stored Procedure from SSIS

Execute stored Procedure from SSIS

FORUM

Stored Procedure At The Remote Server

Executing Stored Procedure At Another Server

FORUM

DBCC command execution History

When did a DBCC command executed in a DB

FORUM

SSIS error while executing in command line

SSIS error while executing in command line

Tags
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones