June 26, 2008 at 2:57 pm
hello,
I tried to use the code listed below to write a simple line of string to a text file from an existing stored Procedure. To test the code, I created a text file and run code in sql query analyzer. I did not get any error, however there was nothing added to my test file. Any idea why ?? thanks.
DECLARE @FS int, @OLEResult int, @FileID int, @FileName varchar(255), @Text1 varchar(255)
set @FileName = 'c:\dummy.txt'
set @text1 = 'THIS IS A header file'
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Error: Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'Error: OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'Error: WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
June 26, 2008 at 3:12 pm
You can use the bcp command to write directly to a file.
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
--The character after -t notes the field delimeter. If -t is removed tab is the default.
SET @FileName = REPLACE('c:\Test_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM sysfiles" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -t,'
EXEC xp_cmdshell @bcpCommand
June 27, 2008 at 7:19 am
Ken,
thank you for your respond. Let me tell you what I am trying to complish here. There is a text file was created with a dts package. My task is to insert a header string and a footer string to this text file.
Sherry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply