t-sql result into a file

Question

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 cannot 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

Out of curiosity, if you have full access to the OS, why can't you use the commandline? — billinkc
– billinkc, Commented Sep 15, 2011 at 21:04
the script will be executed by support people of other company and that's the requirement I have been facing. I think it will be a part of some automated process which executes t-sql scripts. — Mariusz
– Mariusz, Commented Sep 15, 2011 at 21:15

James Johnson · Accepted Answer · 2011-09-15 21:33:18Z

5

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')

edited Sep 15, 2011 at 21:33

answered Sep 15, 2011 at 20:53

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mariusz Over a year ago

Thanks James, but it needs to be a t-sql script. It will be auto executed (I think) as a part of a bigger job, no human interaction involved. Otherwise I wouldn't be here asking for help :-)

Mariusz Over a year ago

James, I heard about OPENROWSET and will try it but isn't an Office on the machine requirement?

Mariusz Over a year ago

INSERT INTO OPENROWSET gives an error: Incorrect syntax near ';'.

billinkc Over a year ago

You wouldn't need a full office install, we were able to get by with just installing the Access database engine (not Access or Office itself)

billinkc · Accepted Answer · 2011-09-15 22:05:48Z

2

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.

answered Sep 15, 2011 at 22:05

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

1 Comment

Mariusz Over a year ago

That probably will be the option for me. Thank you very much :-)

Carth · Accepted Answer · 2011-09-16 00:16:15Z

1

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/.

edited Sep 16, 2011 at 0:16

answered Sep 15, 2011 at 20:55

Carth

2,3431 gold badge17 silver badges27 bronze badges

4 Comments

Mariusz Over a year ago

I have to deliver a t-sql script that can be executed from within SSMS. It won't be me who will be doing this unfortunately.

Carth Over a year ago

If you can't use the suggestions in the Phil Factor examples can you let us know whether or not you're able to create static database objects (procs, jobs, functions, etc.) on the system to be run later by the tsql?

Mariusz Over a year ago

script will be run under full machine admin privileges I believe, so yes

Mariusz Over a year ago

I have just checked this fact again and no, there will be an error: SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server.

Collectives™ on Stack Overflow

t-sql result into a file

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related