SQL: Write to Text File

Friday, December 31, 2010

CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS

DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file

execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1

execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1

IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID

EXECUTE @OLEResult = sp_OADestroy @FS

SQL