Solved

Creating a text file from within a stored Proc

Posted on 2004-08-20
10
1,399 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
[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
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
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 …

695 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