Write to text file from a stored procedure

Question

Post reply

Write to text file from a stored procedure

delessio

SSC Rookie

Points: 45
More actions
September 28, 2007 at 5:55 am

#103415

I have a stored procedure that is executed by a user, by running a crystal report - and I want the report to reflect any error produced or a message that a file was created and the location. So, the stored proc needs to be able to print results to a .txt file. How can I do this?
Any help would be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

david-569361 Grasshopper Points: 20 More actions · Answer 1

Hi,

Did you find out how to do it?

if yes, if you can share it.

Thank You!

David

delessio SSC Rookie Points: 45 More actions · Answer 2

I have not received any solutions to this issue other than using DTS, but that does not fit my needs. If I can figure this out, I will post a solution.

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 3

From within a stored procedure you can use the extended stored procedure master..xp_cmdshell. So if you want to write to a text file you can use something like:

exec master..xp_cmdshell 'echo "Test message" > C:\testfile.txt'

This is like running the echo message and redirecting to the file on the command line.

delessio SSC Rookie Points: 45 More actions · Answer 4

Thanks for the response. I have looked into that, but I have restrictions that prevent my access to master..xp_cmdshell (government restrictions). Do you know of any other way to write to a file from within a stored procedure without using master..xp_cmdshell or setting up a DTS?

Thanks for your input and suggestions.

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 5

There are two other ways you can explore:

1. Create a linked server to the text file and then you can use INSERT queries like you do inserts into a normal table.

2. Use OPENROWSET clause to write directky to the text file.

I havent researched much with text files but believe that these two have the capability to read and write to text files from T-SQL. Check out the link: http://thiagsundar.wordpress.com/export-data-to-text-file/