sp_OAMethod not returning anything...what's up? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp_OAMethod not returning anything...what's up?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2005-08-04 : 23:24:45

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

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

--PhB

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-08-05 : 00:29:20

Works for me OK....

Try calling sp_OAGetErrorInfo after sp_OAMethod/Create to get back specifics about the problem(s). It could be a simple permissions issue. Are you running this interactively or part of a job?

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources