To write to a text file

,

/*
--Objective : To Write a given string on to a given file
--Created by: Helen
--Date : Jan 4, 2005
--Execution : SELECT * FROM DBO.Ufn_WriteToFile('D:\Testing\dbcc.TXT','Hello world')
*/

CREATE function dbo.Ufn_WriteToFile
(
	@FileName varchar(1000), @Text1 varchar(1000)
)
RETURNS VARCHAR(100) 
AS
BEGIN
	DECLARE @status VARCHAR(100), @eof VARCHAR(10)
	SET @status = 'SUCCESS'
	DECLARE @FS int, @OLEResult int, @FileID int
	
	EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
	
	IF @OLEResult <> 0 
		SET @status= 'Error: Scripting.FileSystemObject'
	
	--Open a file
	execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 8, 1 -- Append if required (8)
	--execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName,  1 
	IF @OLEResult <>0 
		SET @status ='Error: OpenTextFile'
	
	--Write Text1
	execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
	IF @OLEResult <> 0 
		SET @status= 'Error : WriteLine'
	
	EXECUTE @OLEResult = sp_OADestroy @FileID
	EXECUTE @OLEResult = sp_OADestroy @FS
	RETURN @status
END
GO

Rate

Share

Share

Rate