write to a text file from SPROC

• raiders0724

  SSC Veteran

  Points: 265

  More actions

  June 26, 2008 at 2:57 pm

  #93496

  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

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  June 26, 2008 at 3:12 pm

  #835001

  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

• raiders0724

  SSC Veteran

  Points: 265

  More actions

  June 27, 2008 at 7:19 am

  #835318

  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