sp_OAMethod returns an error - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

sp_OAMethod returns an error

Author

Topic

hkuntal
Starting Member

3 Posts

Posted - 2007-12-06 : 08:56:39

I am writing insert scripts repeatdly in to a single text file from a store proc.So my store proc gets called repeatdly.

The SP executes correctly for some 200 times but fails there after.
On debugging I found that the following script

EXECUTE @hr = sp_OAMethod @objFileSystem,'OpenTextFile', @objTextStream OUTPUT, @FileAndPath, 8

returns a non-zero @hr value that is failing it.I dont know why does this fail midway.Pls can any one help me.

This is my complete store proc
ALTER PROCEDURE [dbo].[SP_DELTA_MIGRATION_SCRIPT]
(
@String VARCHAR(8000), --8000 in SQL Server 2000
@Path VARCHAR(300),
@Filename VARCHAR(100)
)
AS
DECLARE @objFileSystem INT,
@objTextStream INT,
@Append INT,
@objErrorObject BIGINT,
@strErrorMessage VARCHAR(1000),
@Command VARCHAR(1000),
@hr BIGINT,
@fileAndPath VARCHAR(80)

--SET NOCOUNT ON

SELECT @strErrorMessage='opening the File System Object'

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

SELECT @FileAndPath=@path+'\'+@filename
IF @HR=0 SELECT @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+ @FileAndPath +'"'

--- Check File already exist
EXEC sp_OAMethod @objFileSystem, 'FileExists', @Append out, @FileAndPath

IF @Append = 1
BEGIN
--open the text stream for append
IF @HR=0 EXECUTE @hr = sp_OAMethod @objFileSystem,'OpenTextFile', @objTextStream OUTPUT, @FileAndPath, 8

END
ELSE
BEGIN

--Create the text file for write
IF @HR=0 EXECUTE @hr = sp_OAMethod @objFileSystem,'CreateTextFile',@objTextStream OUTPUT,@FileAndPath,-1

END

IF @HR=0 SELECT @objErrorObject=@objTextStream,

@strErrorMessage='writing to the file "'+@FileAndPath+'"'

IF @HR=0 EXECUTE @hr = sp_OAMethod @objTextStream, 'WriteLine', NULL,@String

IF @HR=0 SELECT @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
IF @HR=0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Close'

IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source OUTPUT,@Description OUTPUT,@Helpfile OUTPUT,@HelpID OUTPUT
SELECT @strErrorMessage='Error whilst '
+COALESCE(@strErrorMessage,'doing something')
+', '+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXECUTE sp_OADestroy @objTextStream

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-06 : 12:35:23

I would guess that the file is locked - probably windows hasn't released it - maybe doing something asynchronously?
Or could be a problem with the com SPs - they're better than they used to be but I wouldn't do this repeatedly as you are.
I would probably write to a separate file and then concatenate.

An easier way to write a single line is by using xp_cmdshell and echo
to append
exec master..xp_cmdshell 'echo hello there >> c:\myfile'
For a new file
exec master..xp_cmdshell 'echo hello there > c:\myfile'

Could you try that?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tmenacher
Starting Member

1 Post

Posted - 2011-06-30 : 14:25:54

I encountered the same problem, attempting to write over 1200 individual files using this method. The problem is the operating system cannot create files as fast as the SQL server can spit them out. You need to put a delay in between each create file (like wait 2 seconds), so that you don't overload the system. I think I used the SQL command

WAITFOR DELAY '000:00:05'

in a cursor to pause 5 seconds between each file creation. The process, of course, ran for hours overnight, but the problem was solved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources