Search

Thursday, September 27, 2012

Query to write/create a file

This SQL stored procedure will allow you to write to the file on your system where 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.

You need to reconfigure some advanced SQL server settings to use below stored procedure. 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
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
--To enable Ole automation feature
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Now, the stored procedure to create file in local system: 


Create Procedure  [dbo].[USP_SaveFile](@text as NVarchar(Max),@Filename Varchar(200)) 
AS
Begin
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
Begin
    Exec @rc = master.dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append
End
Exec @rc = master.dbo.sp_OADestroy @Object 
End

In this procedure 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 write binary files to the file system, you just need to check and change the file extension in the second parameter. 

EXEC USP_SaveFile 'Writing data to text file', 'D:\Temp\MSSQL.txt'


1 comment:

  1. • I very much enjoyed this article. Nice article thanks for given this information. I hope it useful to many Peopledata since Online Training hyderabad

    ReplyDelete