?
Solved

Saving to Text file from TSQL

Posted on 2004-09-15
4
Medium Priority
?
13,396 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 2000 total points
ID: 12064786
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
ID: 12064958
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
ID: 20500624

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
ID: 20500765
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

850 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