Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Saving to Text file from TSQL

Posted on 2004-09-15
4
Medium Priority
?
13,352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 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