t-sql result into a file

I have spent few hours searching with no luck today. What I need to do is to write a t-sql query which will be executed from within SSMS and which supposed save an output to a file. I have full admin access to the sql server 2008 r2 as well as to the OS (I believe Win 2008 Server)

I 开发者_如何学Pythoncannot use commandline, batch files etc. It needs to be done straight from SSMS as an execution of a t-sql script.

It doesn't even have to be pretty :-) It just needs to do the job.

I would very appreciate any help.

Regards Mariusz

Query > Results To > Results to File

You can also do a select all on the results pane, and right click the top-left cell. There's an option to "Save Results As," which will allow you save the results in CSV format.

If you're looking for something more advanced, I would suggest looking into SSIS (SQL Server Integration Services), which is the replacement for DTS. This is what's usually used for data file exports (DFEs)

EDIT

If you need to export the results to file via script, try this:

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product') 

I can think of two options, given your constraints

• BCP would be the best option, use it with the queryout option I spaced on bcp being a command line utility.

• Create a sql agent job that has a single step which is an OS command. THe job has a single step which runs a sqlcmd which runs the query and redirects the output to a file. After creating the job, the script runs it and then deletes the job and any history. That is a blecherous solution that would delight Rube Goldberg but would satisfy the requirements of being done completely through SSMS/automated approach.

If you're within SSMS the option to output to a text file is one of the "Results to ..." options on the toolbar alongside the execute button. Are you trying to something more programmatic?

If you have permission to execute xp_commandshell from SSMS you may be able to take advantage of some of the techniques suggested by the psuedonymous Phil Factor at http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/.