Reading and writing files in StoredProcedure

I don’t know why I didn’t thought about reading a text file from a stored procedure until I came across a requirement in SSRS.First I searched for something like usp_OpenFile and usp_ReadFile as usual.But no results.After that I realized that I can interact with system from SQL Server only through the Ole Automation.

It was another new thing to me.I have never used Ole Automation through SQL Server.It is not enabled in SQL server by default.We need to enable through the statement sp_configure.

First I tried to see the status of Ole Automation using the query

EXEC sp_configure 'Ole Automation Procedures';
GO

But this returned error saying that this is an advanced option.Then I turned on the advanced options by the below query

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

After this I turned on Ole Automation.

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Now the environment is ready for Ole Automation.Just need to execute the SQL which opens and reads a file using COM objects.

DECLARE 
        @strPath VARCHAR(255),
        @objFileSystem int,
        @objTextStream int,
        @strRead Varchar(8000),
        @strCommand varchar(1000),
        @HRESULT int,
        @YesOrNo INT 
--Initialize variables        
select @strPath='c:\temp\test.txt'
select @strRead=''

--Creating the COM File System Object'
EXECUTE @HRESULT = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT
--HRESULT will be 0 on success
if @HRESULT=0 select @strCommand=@strPath
--Open file
if @HRESULT=0 execute @HRESULT = sp_OAMethod   @objFileSystem  , 'OpenTextFile', @objTextStream OUT, @strCommand,1,false,0
--Checking for EOF
if @HRESULT=0 execute @HRESULT = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
--Reading file
IF @YesOrNo=0  and @HRESULT=0 execute @HRESULT = sp_OAMethod  @objTextStream, 'ReadAll', @strRead OUTPUT
--Displays the read text.
select @strRead

-- Closing the file "'
if @HRESULT=0 execute @HRESULT = sp_OAMethod  @objTextStream, 'Close'
EXECUTE  sp_OADestroy @objTextStream

Its not easy if you are not from the COM world.It’s VB equivalent code can be found here in msdn.

You can use the same Ole Automation to write into file as well. Make it as function by returning the variable @strRead.If you just want to run this, copy above code  into a query window of SQL Server Management Studio and press F5.I am not sure whether this will work in other databases except SQL server 2008.

Happy scripting…