• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13512
  • Last Modified:

Saving to Text file from TSQL

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.FileSystemObject' , @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
0
billy21
Asked:
billy21
  • 2
1 Solution
 
paeloCommented:
This is my append procedure:


ALTER PROCEDURE procFileSystemAppend(
@FileName varchar(255),
@Text1 varchar(8000)
)
AS
BEGIN


/*
Use Scripting.FileSystemObject to append text to a file.  Will not overwrite specified file
name.
*/


--DECLARATIONS
DECLARE @FS int
DECLARE @OLEResult int
DECLARE @FileID int


--ESTABLISH CONNECTION TO OBJECT
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
      PRINT 'Error while instancing Scripting.FileSystemObject'
      RETURN -1
END


--OPEN FILE SPECIFIED BY @FileName AND RETRIEVE @FileID LINK
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0
BEGIN
      PRINT 'Error while executing method OpenTextFile'
      RETURN -1
END


--APPEND @Text1 TO FILE
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0
BEGIN
      PRINT 'Error while executing method WriteLine'
      RETURN -1
END


--DEALLOCATE
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS


--RETURN SUCCESS
RETURN 1

END
0
 
HilaireCommented:
Another version that checks file existence to choose between create and append


Create Proc UCreateOrAppendTextFile(@Filename VarChar(100),@Text nVarchar(4000))
AS
     DECLARE @FileSystem int
     DECLARE @FileHandle int
     DECLARE @RetCode int
     DECLARE @RetVal int
     DECLARE @CreateOrAppend int
     
     
     EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
     IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
          RAISERROR ('could not create FileSystemObject',16,1)

     EXECUTE @RetCode = sp_OAMethod @FileSystem , 'FileExists', @RetVal out, @FileName
     IF (@@ERROR|@RetCode > 0)
          RAISERROR ('could not check file existence',16,1)
     -- If file exists then append else create    
     SET @CreateOrAppend = case @RetVal when 1 then 8 else 2 end
     EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @Filename, @CreateOrAppend, 1
     IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
          RAISERROR ('could not create File',16,1)
     
     EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WriteLine' , 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
0
 
jedimikeCommented:

Does anybody know how to set the encoding on this to ANSI?  The file I get by default is Unicode.
0
 
HilaireCommented:
You could try passing a 4th parameter to the OpenTextFile method
Passing 0 (zero) should force ASCII type

http://msdn2.microsoft.com/en-us/library/314cz14s.aspx

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now