Use Sql query to write/create a file

11. December 2009 14:33

SQL Server T-SQL  6 Comments

This SQL stored procedure will allow you to write to the file on your file system. Note the file system will be the the same on which the SQL server is running. If you are using this with your local SQL server then it will write and create files on your local file system and if you are on the remote machine, the file system will be the remote file system.

Create Procedure  [dbo].[USP_SaveFile](@text as NVarchar(Max),@Filename Varchar(200)) 
declare @Object int,
        @rc int, -- the return code from sp_OA procedures 
        @FileID Int 
EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT 
EXEC @rc = sp_OAMethod  @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1 
Set  @text = Replace(Replace(Replace(@text,'&','&'),'<' ,'<'),'>','>')
EXEC @rc = sp_OAMethod  @FileID , 'WriteLine' , Null , @text  
Exec @rc = master.dbo.sp_OADestroy @FileID   
Declare @Append  bit
Select  @Append = 0
If @rc <> 0
    Exec @rc = master.dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append

Exec @rc = master.dbo.sp_OADestroy @Object 

But before you start using this procedure you need to reconfigure some advanced SQL server settings. Use the below configuration query to enable 'OLE Automation Procedures'. If this is not enabled and you try executing the procedure you will get errors.

Use master
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
--To enable Ole automation feature
EXEC sp_configure 'Ole Automation Procedures', 1;

The first parameter will take the text to be written to the file and the second parameter will take the complete path of the file to be created with the text in it. You can also use the same procedure to wite binary files to the file system, you just need to check and change the file extension in the second parameter. Usage:

EXEC USP_SaveFile 'Microsoft SQL Server 2008', 'C:\MSSQL.txt' 
Currently rated 4.0 by 1 person

Comments (6)

Kishan Kishan
2/24/2010 10:17:13 AM #

Hi,Prashant, I need a help in blogengine. About how to integrate flash & tabbed panel in blogengine.Can u help me please. Thank You Kishan Srivastatva

Alex Alex
9/16/2011 10:06:08 PM #

Of course, there are many ways to do SQLs and the method you describe will have it's own application niche. Mine is a little more straightforward as you actually only need to SQL itself and the proper command line options for OSQL. The reason I resorted to it is that the query I had to "fix" was almost impossible to fix without a complete overhaul. It also crashes the server with "out of memory error"... then I tried Java and Grails with similar results, running out of memory or stopping due to GC not being "efficient". The solution although simple was not evident until I took a big step back and looked from a different point of view. The query was placed in a text file named "c:\tmp\query.sql" and output will be writen to "c:\tmp\output.txt". With that in place, just issue the command: osql -E -n -s "|" -w 1000 -h -i c:\tmp\query.sql -o C:\tmp\output.txt The output file will contain headers, breaking line at 1000th position (seems the default is 76) using the character "pipe" as separator. See the output of "osql -h" for the help on the options.

kumar kumar
2/23/2015 10:51:39 AM #

Hi Your Code Not working . can u help me.

Prashant Prashant
2/25/2015 3:34:15 AM #

@kumar: What version of SQL server you are using?

Jimmy Vivas Jimmy Vivas
6/9/2015 9:07:44 PM #

I can not get this to work in SQL Server 2012

Prashant Prashant
6/11/2015 2:57:45 AM #

@Jimmy: The query was tested on SQL 2008 because as that time this was the only version of SQL server I can get my hand on. You can try this:

Add Comment

Visit to discover Indian blogs Computers Blogs