Solved

Saving to Text file from TSQL

Posted on 2004-09-15
4
13,228 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

724 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