We help IT Professionals succeed at work.

Reading a file from the filesystem using SQL

In a SQL Stored Procedure, I write a file to the filesystem using teh following code:

DECLARE @dataToWrite VARBINARY(MAX),
      @ObjectToken INT
           
            EXEC sp_configure 'show advanced options', 1
            RECONFIGURE
            EXEC sp_configure 'Ole Automation Procedures', 1
            RECONFIGURE
 
             EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
            EXEC sp_OASetProperty @ObjectToken, 'Type', 1
            EXEC sp_OAMethod @ObjectToken, 'Open'
            EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @dataToWrite
            EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'filename', 2
            EXEC sp_OAMethod @ObjectToken, 'Close'
            EXEC sp_OADestroy @ObjectToken

            EXEC sp_configure 'Ole Automation Procedures', 0
            RECONFIGURE
            EXEC sp_configure 'show advanced options', 0
            RECONFIGURE
           

Now, if I want to READ a file FROM teh filesystem, how do I go about that?!?!?!

Anyone?
Comment
Watch Question

Howdy

I think this is what you are looking for.
declare @mytext varchar(8000)
declare @objFSys int 
declare @objFile int 
declare @blnEndOfFile int
declare @strLine varchar(4000)

exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out 
exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, @File, 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
while @blnEndOfFile=0 begin
exec sp_OAMethod @objFile, 'ReadLine', @strLine out
set @mytext=@mytext+char(13)+@strLine
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
end
exec sp_OADestroy @objFile
exec sp_OADestroy @objFSys 
print @mytext

Open in new window

Top Expert 2012

Commented:
Or tad simpler you could use the undocument function xp_readerrorlog and avoid all that sp_OA* functions.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Or use an adhoc linked server using the MS text driver....

SELECT result FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','SELECT * FROM c:\ee\mytextfile.txt')

And you can then use that to select into a staging area, or unpack the file etc....
Top Expert 2012

Commented:
I agree.  Anything is better than the sp_OA* functions.  Although you could argue that this should not be accomplished using SQL Server in the first place ...
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
*laughing* you dont really want me to argue do you ???

Author

Commented:
None