How to output query results into a text file

Question

How to output query results into a text file

gary.dunn

SSC-Addicted

Points: 412
More actions
April 28, 2011 at 3:20 pm

#249902

I have a user who wants the results of a query stored into a file. I can run the query in SSMS and I can save the results in a file by right clicking on the output and then selecting "Save results as...". But how can I set this up for the user so it happens automatically? I'd like to set it up as a job that I can schedule as part of Maintenance Plan or something similar to that. Thanks in advance!

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

do you have SSIS available to you. this would be very easy to do in an SSIS package and then you could execute the package from a job.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

gary.dunn SSC-Addicted Points: 412 More actions · Answer 2

By SSIS I assume you mean SQL Server Integration Services? Yes, that is installed...I connected to it and I see two folders: Running Packages and Stored packages. But that's it. I'll look around on this web site for a tutorial. But if you have any insights or shortcuts for me on how to proceed, I'm all ears!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

You can utilize BCP commands and call xp_cmdshell to execute the BCP statements..

To read abt BCP, click here

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

BCP is a good option. Note that xp_CmdShell is disabled by default in new SQL Server installations due to it increasing the attackable surface area of an instance when enabled. Avoid it where possible. I would opt to call BCP from the command line directly if it's a one-time need or within a PowerShell script if you need a bit more of a programming environment to setup the call and handle the results or do more tasks after the call.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Pam Brisjar SSChampion Points: 12094 More actions · Answer 5

Simplest solution, really is to look at sp_send_dbmail. Set up one command into a job and it will run the query and mail it off to whoever you tell it to in one step.

Amu SSCommitted Points: 1691 More actions · Answer 6

You can use OSQL or SQLCMD in your SQL job and through the query output into shared directory on your SQL server where your user has permission to read.