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


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

5 comments:

Anonymous said...

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

Me said...

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

I appreciate the help!

Thanks,
-jon

Anonymous said...

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

Burak TARHANLI said...

Thanks for the blog. :)

Anonymous said...

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 .