December 22, 2007

Save SELECT query output to a text file




Ever needed to save the result of your SELECT query to a text file. Well use xp_cmdshell. xp_cmdshell is an extended stored procedure kept in the master database. It issues OS commands directly to the Windows command shell. You need to be a member of the sys_admin group to use this command or have the xp_sqlagent_proxy_account.

To save your SELECT query results to a text file, use this query :

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 CUSTOMERID FROM Northwind.dbo.Customers" queryout "c:\text.txt" -c -T -x'

One word of caution. xp_cmdshell operates synchronously. So the caller has to wait for the control to be returned to him/her until the command-shell command is completed.

Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

5 Responses to "Save SELECT query output to a text file"
  1. Anonymous said...
    February 6, 2009 at 1:20 PM

    Thanks for the blog. I went through quite a few pages before your page until i got bcp working :)

  2. Me said...
    March 13, 2009 at 8:02 AM

    Thanks, This is just what I was looking for to help my coding be quite a bit simpler.

    I appreciate the help!

    Thanks,
    -jon

  3. Anonymous said...
    September 21, 2009 at 11:54 AM

    Thank for the blog. But How I can include the titles of the tables in the text file?

  4. Burak TARHANLI said...
    June 1, 2011 at 6:43 AM

    Thanks for the blog. :)

  5. Anonymous said...
    August 13, 2013 at 8:34 AM

    Can you please let me know if we can write a script to copy the result messages(which are displayed in Results Pane) and write it to a file .

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions