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
But this returned error saying that this is an advanced option.Then I turned on the advanced options by the below query
After this I turned on Ole Automation.
Now the environment is ready for Ole Automation.Just need to execute the SQL which opens and reads a file using COM objects.
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.