Query Output in a text file

Question

Query Output in a text file

haichells

SSCrazy

Points: 2708
More actions
December 2, 2010 at 3:37 am

#241968

Hi,
My requirement is to run a query and the o/p of the query has to go into a text file (new file to be created). Do we have any workarounds for acheiving this?
Thanks in Advance!

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

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

Meet George Jetson SSChampion Points: 11539 More actions · Answer 1

Maybe I am misunderstanding your question, but to get the results from a query into a text file either execute the query by CTRL+SHIFT+F or change the setting via Query | Results To | Results To File. Either way, when executed will then display a request for where to save the output and what format you wish to use.

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

Sigerson Hall of Fame Points: 3697 More actions · Answer 2

What kind of text file? If it's a CSV or other delimited type of file you can do it with bcp very easily. Look it up in BOL.

To be able to call bcp (a command-line function), you'll need to

1. Set the configuration option 'show advanced options' to 1. Issue a RECONFIGURE command

2. Set the configuration option 'xp_cmdshell' to 1. Issue another RECONFIGURE command

There's a slight security exposure while these two are on, so be sure to set them back to zero (0), in reverse order, when you're done with the file upload.

Sigerson

"No pressure, no diamonds." - Thomas Carlyle

Amit Raut SSCrazy Points: 2613 More actions · Answer 3

Are you trying to create a new text file and write to it within SQL query only?

Following would be a helpful snippet for how you may achieve that.

DECLARE @Text AS VARCHAR(100)

DECLARE @Cmd AS VARCHAR(100)

SET @Text = 'Hello world^| '

SET @Cmd ='echo ' + @Text + ' > C:\AppTextFile.txt'

EXECUTE Master.dbo.xp_CmdShell @Cmd

Although this is really not a good way to write data to a text file: usually SQL Server should not have permission to write to the root of the C: drive, and xp_cmdshell is disabled by default.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 4

Although I think this post definitely deserves an answer, be aware that the OP might not respond because it is a 3 year old post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)