How to read in a text file from a stored procedure

Question

I need to read in a log file created by a SSIS package in my stored procedure.

Is there a good way to do this?

I tried using this code but it shows the contents of my file as jibberish. Is there some kind of encoding issue it's not handling? Is there an easier way?

Answer 1

Have you tried a straight bulk insert? For example:

create table #TempTable (line varchar(256))
bulk insert #TempTable from 'c:\logfile.txt'

Answer 2

You can try

select * from OPENROWSET(BULK N'C:\logfile.txt', SINGLE_BLOB)

Answer 3

Stored Procedures doing File Manipulation doesnt sound like a good idea!

Maybe you can explain the scenario in more detail that might help people suggest maybe a better way out.

However, if you HAVE TO, i think you should look at CLR Stored Procedures in SQL Server. That way, you can do the file manipulation using regular .NET classes

Refer these MSDN Link 1 and MSDN Link 2 links for more details

This particular link is related to permissions that need to be given to the assembly for it to be able to access external resources like files from a CLR procedure in SQL Server