December 2, 2010 at 3:37 am
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!
December 2, 2010 at 9:29 am
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.
November 22, 2013 at 12:56 pm
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
November 27, 2013 at 1:18 pm
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.
November 27, 2013 at 3:51 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply