Outputing query results to a text file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Outputing query results to a text file

Author

Topic

mr2turbo18
Starting Member

10 Posts

Posted - 2007-03-22 : 13:55:41

Hello,

I couldn't find it in BOL, but I'm pretty sure it can be done.

I'd like to save a query into a text document on the file server. Without having to use the QA menu.

So for example:
select *
from dbo.BookTitles
order by title
OUTPUT TO FILE '\\server\drive\books\BookTitles.txt'
--Last line is a guess on how it would work.

Anybody know?

-Richard the Newb

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-22 : 14:19:36

Either use BCP with QUERYOUT option or go to Tools, Options, Query Results, Results to File.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-22 : 17:37:22

also sqlcmd.exe will do it for you.

www.elsasoft.org

mr2turbo18
Starting Member

10 Posts

Posted - 2007-03-23 : 08:31:15

Thanks for the quick responses. Are there any options for a command that will work within query analyzer? I can use the menu but its less efficient than setting the code up in batch.

-Richard the Newb

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-23 : 12:55:11

in QA you have to use the menu. there may be a shortcut for the menu. there's no way to specify in tsql that you want the result to a file though.

btw I have often thought this feature was missing - we have BULK INSERT in tsql, so why isn't there a BULK EXPORT also? bcp does it both ways, but the export side of things is missing in tsql. oh well. just use bcp.

www.elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-23 : 14:36:59

...oh for the love of...

EXEC master..xp_cmdshell 'bcp "SELECT * FROM Northwind.dbo.Orders" QUERYOUT d:\test.dat -c -S<servername> -T'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-23 : 15:05:47

whatever. some of us prefer not to have to enable that beast...

www.elsasoft.org

mr2turbo18
Starting Member

10 Posts

Posted - 2007-03-23 : 15:18:31

Thanks Brett!

-Richard the Newb

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-23 : 15:29:24

quote:
Originally posted by jezemine

whatever. some of us prefer not to have to enable that beast...

www.elsasoft.org

ummm, why?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-23 : 15:54:09

because it poses a security risk. it increases the "surface area" of attack.

www.elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 09:47:33

Anyone else want to comment this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-26 : 09:52:56

I vote for not using xp_cmdshell.

If you are going to use BCP, why you need an indirect way of doing it through QA and increasing the risk. Why not do it straight-away from the command-line?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 10:04:02

What's the difference between having a bat file that does this, or a sproc that you rights over? Let's say you want to interogate a file loaction to see what files are there, want to load, then archive.

How would you do that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-26 : 10:22:13

My point is that the things which you can easily accomplish through the means outside SQL server, should not be done inside SQL Server unless you are out of options.

By giving xp_cmdshell access to a user, you are opening doors for him/her to do any kind of manipulations he want, which are otherwise not possible.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 10:26:25

quote:
Originally posted by harsh_athalye

By giving xp_cmdshell access to a user, you are opening doors for him/her to do any kind of manipulations he want, which are otherwise not possible.

Well thank goodness I'm not doing that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-03-26 : 11:14:42

quote:
Originally posted by jezemine

whatever. some of us prefer not to have to enable that beast...

www.elsasoft.org

Well if not needed, no need to enable it.
But I agree with Brett, that xp_cmdshell is really handy in lots of situations.

I don't think it poses a very serious threat, if the basic sql server set up is done well.

1. Sql Server runs under a windows account with controlled privileges, and xp_cmdshell executes under that account.
2. Only sql administrators are able to execute xp_cmdshell

If someone breaches security and is able to run sql as sql admin or gains access to the OS, I have more serious problems than the xp_cmdshell issue.

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 12:36:41

quote:
Originally posted by rockmoose

If someone breaches security and is able to run sql as sql admin or gains access to the OS, I have more serious problems than the xp_cmdshell issue.

rockmoose

yes, that would be bad

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-26 : 12:52:14

I guess my point was, I don't have it enabled, and I am not going to enable it just so I can call bcp from within a proc.

www.elsasoft.org

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-03-26 : 13:37:33

quote:
Originally posted by jezemine

I guess my point was, I don't have it enabled, and I am not going to enable it just so I can call bcp from within a proc.

www.elsasoft.org

Or scriptdb.exe