I have the following stored proc (see below) which works very well to save a big chunk of text to a text file. How can this be modified to accept one line at a time and append the text to my text file?
I need to take into account the fact that upon first insert the text file will not exist.
Thanks in advance,
Bill.
Create Proc UCreateTextFile(@Filename VarChar(100),@Text nText)
AS
DECLARE @FileSystem int
DECLARE @FileHandle int
DECLARE @RetCode int
EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObjec
t' , @FileSystem OUTPUT
IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
RAISERROR ('could not create FileSystemObject',16,1)
EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @Filename, 2, 1
IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
RAISERROR ('could not create File',16,1)
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Write' , NULL , @text
IF (@@ERROR|@RetCode > 0 )
RAISERROR ('could not write to File',16,1)
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close'
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not close file ',16,1)
EXEC sp_OADestroy @filehandle
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not destroy file object',16,1)
EXEC sp_OADestroy @FileSystem
Go
Start Free Trial