sp_oamethod to read text file gives blank results. (??)

Question

Post reply

sp_oamethod to read text file gives blank results. (??)

phrankbooth

Right there with Babe

Points: 750
More actions
August 3, 2005 at 8:13 am

#90472

Hello,
I have a plain text file called Orders.txt with the following three lines:
Line1
Line2
Line3
I'm trying to read that text and put it in a table. I'm using the sp_oamethod call as stated below. The code runs and reports that it was successful, but when I query the table it is empty and even the 'print' command doesn't print anything. Seems simple enough, but I can't see what I might be missing. Any help is appreciated.
Thanks in advance!
--PhB
declare @objFSys int
declare @objFile int
declare @blnEndOfFile int
declare @strLine varchar(40)
CREATE TABLE #mytesttable (sometext text)
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
-- Change the file path to the one that is passed to your stored procedure
exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, 'C:\orders.txt', 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
while @blnEndOfFile=0 begin
exec sp_OAMethod @objFile, 'ReadLine', @strLine out
select @strLine
INSERT INTO #mytesttable (sometext) VALUES(@strLine)
print @strLine
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
end
exec sp_OADestroy @objFile
exec sp_OADestroy @objFSys
select * from #mytesttable

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 1

Try this,

Change the "exec sp_OAMethod @objFile, 'ReadLine', @strLine out"

to exec @blnEndOfFile = sp_OAMethod @objFile, 'ReadLine', @strLine out

Then check the value of @blnEndOfFile

Hope this helps,

Darrell

phrankbooth Right there with Babe Points: 750 More actions · Answer 2

Thanks, but that doesn't do it. It just gives me the method's return result which is a long int.

I don't know, it seems so straight forward yet it ain't working.

Denis W. Repke SSC-Addicted Points: 456 More actions · Answer 3

I tried your code exactly as written and it worked perfectly. I tried outputting to both a temporary table (as in your original code) and to a "real" table. In both cases, it worked perfectly. I tried it in a SQL 2000 database from both SQL 2000 Query Analyzer (SP3) AND from SQL 2005 Apr CTP Management Studio.

Denis W. Repke

phrankbooth Right there with Babe Points: 750 More actions · Answer 4

Wow, that's crazy I'm doing this on SQL 200 SP3 QueryAnalyzer logged in as SA.

Like I said it works without error but the table is empty. I'll have to see if my SA doen't have the right permissions.

Thanks to all for the help!