SQL Server‎ > ‎

Create text files from SQL Server data

Creating a text file from SQL data is easy using the stored procedure listed below.  We didn't write it (we found it on the web at  (https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/
) but we know it works.   This is an easy way to output SQL Server data to a text file, or an XML file, or even an HTML web page.

To use the stored procedure:

--Step1 declare variables
Declare @filename nvarchar(100)
Declare @String Varchar(max)
Declare @Path VARCHAR(255)
Set @Path='C:\devin'

--Step 2 build a @string variable with whatever you want the text file to contain

Set @String='
<?xml version='+char(34)+'1.0'+char(34)+ ' encoding='+char(34)+'UTF-8'+char(34)+'?>
<JDF Type='+char(34)+'Combined'+char(34)+' xmlns='+char(34)+'http://www.CIP4.org/JDFSchema_1_1'+char(34)+' ID='+char(34)+'rootNodeId'+char(34)+' Status='+char(34)+'Waiting'+char(34)+' JobPartID='+char(34)+'000.cdp.797'+char(34)

--NOTE: you often need to include single and double quotes in your text file.   You can't build a SQL string with single and double quotes without your code getting really complicated.  So use char(34) and char(39) whenever you want your text file to contain a double quote or a single quote.

--Step 3 call the stored procedure to create the file 
exec spWriteStringToFile @string, @Path, @filename

That's it.   Works great.

You need to create this stored procedure in your database:

create PROCEDURE [dbo].[spWriteStringToFile]
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)
DECLARE  @objFileSystem int
        ,@objTextStream int,
  @objErrorObject int,
  @strErrorMessage Varchar(1000),
     @Command varchar(1000),
     @hr int,
  @fileAndPath varchar(80)
set nocount on
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT
Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
 , @objTextStream OUT, @FileAndPath,2,True
if @HR=0 Select @objErrorObject=@objTextStream,
 @strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String
if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'
if @hr<>0
  @Source varchar(255),
  @Description Varchar(255),
  @Helpfile Varchar(255),
  @HelpID int
 EXECUTE sp_OAGetErrorInfo  @objErrorObject,
  @source output,@Description output,@Helpfile output,@HelpID output
 Select @strErrorMessage='Error whilst '
   +coalesce(@strErrorMessage,'doing something')
   +', '+coalesce(@Description,'')
 raiserror (@strErrorMessage,16,1)
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream