Solved

Creating a text file from within a stored Proc

Posted on 2004-08-20
10
1,397 Views
Last Modified: 2013-11-18
I am using this code (From this web site) to create a text file within a stored procedure. It seems work but the file created is not a text file. it doesn't have .txt extension at the end of the file. How should I  change the code to make the file have the .txt extension. Thanks for help.

DECLARE     @FileSystem int
DECLARE     @FileHandle int
DECLARE @Filename varchar(32)
DECLARE     @text nvarchar(4000)
DECLARE     @RetCode int

SELECT     @text = 'This is some text to write to a file.',
     @Filename = 'C:\MyFile.txt'

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
0
Comment
Question by:CochiseCounty
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11852465
>>It seems work but the file created is not a text file. it doesn't have .txt extension at the end of the file. <<
I suspect it is a text file, it just does not have the .txt extension.

>>How should I  change the code to make the file have the .txt extension. <<
Try adding '.txt' to the file name?
0
 
LVL 12

Expert Comment

by:kselvia
ID: 11854290
Your code works fine for me

master..xp_cmdshell 'type c:\myfile.txt'

output                              
-------------------------------------
This is some text to write to a file.

Or a quick hack if it will suffice;

declare @sql varchar(255)
set @sql = 'exec master..xp_cmdshell ''echo This is some text to write to a file.>c:\text.txt'''
exec (@sql)
0
 

Author Comment

by:CochiseCounty
ID: 11854489
declare @sql varchar(255)
set @sql = 'exec master..xp_cmdshell ''echo This is some text to write to a file.>c:\text.txt'''
exec (@sql)

This works fine for me too. Thanks. If I want to replace the text 'This is some text to write to a file' with a parameter. How should I change the code.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 10

Accepted Solution

by:
Jay Toops earned 250 total points
ID: 11854724
Like this

declare @sql varchar(255)
declare @myParam varchar(1024)
set @myParam='this is my text now'
set @sql = 'exec master..xp_cmdshell ''echo ' + @myparam + '>c:\text.txt'''
exec (@sql)

Jay
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11854783
Umm hey thanks for the points but  kselvia deserves at least half the points..
he did most of the work ..

Jay
0
 

Author Comment

by:CochiseCounty
ID: 11854822
I am new to this site and didn't pay attention to points, sorry for that.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 11854875
No problem. Be careful about putting > or | in your text. It will break the echo redirect.  There is also a limit on the length of a dos cmd line, but I don't know what it is.

If you really wanted to avoid all that, do this instead

create table ##mytable (sometext varchar(2000))
insert ##mytable select 'some text to put in a file'
exec master..xp_cmdshell 'bcp "SELECT sometext from tempdb.dbo.##mytable" queryout c:\myfile.txt -c -S<yourservername> -T'
drop table ##mytable
master..xp_cmdshell 'type c:\myfile.txt'
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11854877
Np.. i just post a request to http://www.experts-exchange.com/Community_Support/
asking to award kselvia half the points for this question ..

Or i can do it if you like...
0
 
LVL 2

Expert Comment

by:jedimike
ID: 20500626

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

Expert Comment

by:Anthony Perkins
ID: 20501469
Try posting your own question, instead of piggy-backing on one that is not only closed, but more than 3 years old.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Convert formula to max at a specific date by month 18 21
sql 2014,  lock limit 5 32
SQL Server 2012 r2 - Sum totals 2 25
Find SQL query used by application 3 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

803 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