Solved

Saving to Text file from TSQL

Posted on 2004-09-15
4
13,037 Views
Last Modified: 2012-08-13
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
Comment
Question by:billy21
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
paelo earned 500 total points
Comment Utility
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
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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
 
LVL 2

Expert Comment

by:jedimike
Comment Utility

Does anybody know how to set the encoding on this to ANSI?  The file I get by default is Unicode.
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now