Read Text File Contents

[togatown]

Most of what I've seen here deals with importing data from external file.

I need to read the entire contents of a text file into a variable.

How can I accomplish this.

Thanks,

Toga

 

[mrdenny]

You'll need to put it into a table first, then load it up into a variable as needed. You can use a table variable if you'd like, or a temp table. Then select from that table into your variable.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[togatown]

Thanks Denny,

I worked this out:

DECLARE @RetCode int , @FileSystem int , @FileHandle int
Declare @FileName varchar(11)
Declare @FileOutput as varchar(8000)

Set @FileName = 'C:\test.htm'

EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT

EXECUTE @RetCode = sp_OAMethod @FileSystem, 'OpenTextFile', @FileHandle OUTPUT, @FileName

EXECUTE @RetCode = sp_OAMethod @FileHandle , 'ReadALL' , @FileOutput OUTPUT

EXECUTE sp_OADestroy @FileHandle

EXECUTE sp_OADestroy @FileSystem

Toga