Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Simple way to export SQL Server data to Text Files

MSSQLTips author Ken Simmons By:   |   Read Comments (20)   |   Related Tips: More > Import and Export

Problem
Since the transition from DTS to SSIS, I have found myself looking for alternative ways to do simple tasks such as exporting data from SQL Server into text files. SSIS is a far more powerful tool than DTS, but I generally try to stay away from it for simple operations.  With the combination of a few T-SQL commands this tip shows you a simple way to export data to text files.

Solution
One of the ways I have worked around this is by using bcp with xp_cmdshell. It's fast, easy and I can integrate it right into my code.

The first thing you have to do is make sure xp_cmdshell is enabled. You can do this in one of two ways.

1. You can use sp_configure and execute the following script.

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

2. You can use the Surface Area Configuration Tool.

Select Surface Area Configuration for Features and check the Enable xp_cmdshell checkbox.


Now that you have xp_cmdshell enabled you are ready to export your files.

Here is a sample command that will export the results of the sysfiles table to a comma delimited file called bcptest.txt. Just copy and paste this into a query window and execute the query.

EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,'

Note: BCP is a command line utility and xp_cmdshell is only required to use it within a SQL Batch.

This is the output for the above command when run in the "master" database.

The parameters that were used are:

  • The queryout option allows you to specify a query to export. This could be as simple as the query we have are as complicated as you want.  You can also create a view and select the data from a view.
  • The file name where the results will be stored is placed after the queryout option.
  • The -T parameter specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. You could use -P (password) and -U (user) if you want to use a SQL Server login.
  • The -c specifies the character dataype will be used for each field.
  • The -t parameter allows you to specify a field delimiter. The character after -t will be used to separate the data fields. If -t is removed, tab will be used as the default delimiter.

Another parameter you may need to use is -S to specify the server name.  If you have a named instance, you will need to use this parameter.  Here is an example connecting to server "DEVELOP" and instance "DEV1".

EXEC xp_cmdshell 'bcp "select name, type_desc, create_date from sys.objects" queryout "C:\bcptest2.txt" -T -SDEVELOP\DEV1 -c -t,'

Also be aware that the case of the parameter such as -t and-T makes a difference, so make sure you have correct case for the parameter you are using.  You can find more options for BCP here:  http://msdn.microsoft.com/en-us/library/ms162802.aspx

Next Steps



Last Update: 11/24/2008


About the author
MSSQLTips author Ken Simmons
Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, December 09, 2008 - 1:09:23 AM - mrhappi Read The Tip

 http://www.sqlservercentral.com/scripts/Miscellaneous/31136/

 here's an stored proc that will export and script the object as well...


Wednesday, December 17, 2008 - 1:55:53 PM - Dr DBA Read The Tip

Sort of works with SQL 2005 and SQL 2008


Wednesday, October 05, 2011 - 11:35:54 AM - GlennC Read The Tip

Excellent tip, well illustrated.  It worked the first time, thanks!


Wednesday, March 14, 2012 - 6:24:26 AM - Mike Read The Tip

I'm having trouble with special characters. Do you have any idea on how to prevent a problem like this?

For example, when I open the resulting text file with Notepad2, I get € instead of Ç


Thursday, May 03, 2012 - 3:01:41 AM - Theo Read The Tip

we have designed few tables in the Sql server 2005, DoctorDetails as one of them. It has attributes (DoctorID, FirstName, LastName, Address, Phone_Num, Employment_Type, WardID and Specialization). so i am facing a trouble with a question that says "In a text file , store the details of all doctors who have managed a particular ward in the current month. Make use of the required Tools to perform the data transfer.


Friday, August 17, 2012 - 1:37:00 PM - Krishna Read The Tip

Hello,

How do I extract data from SQL Server table with a hexadicimal delimiter.

In specific, I wanted to extract a table in a delimited file format with hex of '19' (Reall value would be: ^Y) as my delimiter.

Thanks,

Krishna


Tuesday, October 30, 2012 - 12:02:55 PM - Orlando Colamatteo Read The Tip

I am surprised that not one mention was made about the risks of enabling xp_cmdshell, the privileges required to utilize it or  references on how to properly secure it.

A much safer and more robust option is to use SSIS for exporting data from a table to a file. Alternatively you could invoke bcp directly from a PowerShell script. There are other ways as well that do not involve enabling xp_cmdshell. Ideally we should find a way to do this work without ever enabling xp_cmdshell.


Thursday, December 06, 2012 - 10:22:14 AM - Scott Coleman Read The Tip

The simplest way to export data to a text file is probably use Results-to-Text mode in management studio.

Scripts executed by SQLCMD can include ":OUT <filename>" commands to write to a file.

BCP is a very useful and powerful utility, but I wouldn't put it at the top of my "simple" list.

And you would have to have a better reason than simple file export before enabling xp_cmdshell.  If BCP is the preferred export method, the BCP command could be put in a SQL Agent job and then that could be invoked on demand.  Or a SQLCMD script could be used to run BCP with a "!!BCP ..." line, no xp_cmdshell required.


Thursday, December 06, 2012 - 10:23:08 AM - Scott Coleman Read The Tip

Sorry, I meant Results-to-File mode in the first line of my previous comment.


Saturday, December 29, 2012 - 12:11:28 PM - KARTHIKEYAN S Read The Tip

HI ALL

I HAVE ONE TXT FILE,ITS CONTINT 10 COLUMN .I WANT IMPORT 2 COLUMN IN SQL SERVER  WITH BULK INSERT ITS POSSIBLE.KINDLY HELP ME.

 

THANK YOU


Tuesday, January 15, 2013 - 9:23:41 AM - Orlando Colamatteo Read The Tip

I wish Microsoft would implement this suggestion so we are not compelled to enable xp_cmdshell as a way to export data from T-SQL:

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026

As a side note Microsfot recommends using the SQLCLR as a replacement for xp_cmdshell, which I can almost see the writing on the wall, will be deprecated in a future version of SQL Server.


Tuesday, January 15, 2013 - 10:59:13 AM - KaptKos Read The Tip

Why not just perform the query in Mgt Studio and then export it or cut and paste into a text file?  DUH!?!?!


Tuesday, January 15, 2013 - 11:15:46 AM - Greg Robidoux Read The Tip

@KaptKos - yes you could copy and paste the data from SSMS to a text file, but if you have a large data set instead of selecting the data twice you could write out directly to a text file which will be much faster than using copy and paste.


Thursday, April 04, 2013 - 5:27:59 PM - victor Read The Tip

I need ur help. Im working with sql server 2012 express edition. I need to export results from a query into a txt file. I have a query in a .sql file. I just wanna open it and execute. How can i use bcp? I tried with:

bcp "select * from myTable" queryout d:\test.txt -c -T

And SQL server says

Incorrect syntax near 'queryout'

What am i doing wrong?

 

PD

Sorry for my english i speak spanish.

 


Wednesday, May 08, 2013 - 3:09:11 AM - Rajasekhar Pasalapudi Read The Tip
Try the following syntax. Exec xp_cmdshell 'bcp "SELECT * FROM [TESTDB].[dbo].[MYTEST]" queryout "d:\creatFile.txt" -T -c' Include [DBName].[dbo].[tablename] and add Exec xp_cmdshell (if you are executing from SSMS) if above things didn't worked, check bcp enabled or not, if not try following command to enable xp_cmdshell then execute BCP command) here are the commands EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE Exec xp_cmdshell 'bcp "SELECT * FROM [TESTDB].[dbo].[MYTEST]" queryout "d:\creatFile.txt" -T -c' Note: Important thing is if you are exporting specific columns from the table then all the columns should be in a row (i.e. no carriage returns (enter) spaces won't work )

Wednesday, June 26, 2013 - 7:58:03 PM - Mark Childers Read The Tip

Ken:  Thanks for posting.  This worked great and your instructions were accurate and concise.  I searched the entire internet and no one had an anwer as good and efficient as yours.  You're the man.  This will help me to put more automation into some scripts I am using.   


Wednesday, July 24, 2013 - 3:36:27 AM - Maha Read The Tip
I tried this code and got error output SQLState = 08001, NativeError = -1 Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. SQLState = HYT00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login timeout expired SQLState = 08001, NativeError = -1 Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti ons. NULL

Tuesday, October 22, 2013 - 4:24:18 AM - Roni Read The Tip

I tried this code too, but the results appear as follow:

SQLState = 08001, NativeError = -1

Error = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

SQLState = 08001, NativeError = -1

Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config

ured to allow remote connections. For more information see SQL Server Books Online.

SQLState = S1T00, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired

NULL


Friday, February 07, 2014 - 4:51:03 PM - Sam Mcgee Read The Tip

Try this:

 

bcp [Database].[dbo].[tableName]  out  c:\test\name.txt -c -S PCPSRVCNV002\MSSQLSERVER2012 -U sa -P password

 

This work fine in command line

Thanks


Monday, March 24, 2014 - 6:31:38 PM - chimpinano Read The Tip
EXEC DATABASE_NAME..xp_cmdshell 'bcp "select ''id'' as id, ''detail'' as detail, ''creation_date'' as creation_date UNION ALL select convert(varchar,id), detail, convert(varchar,creation_date) from DATABASE_NAME.dbo.TABLE" queryout "C:\file.txt" -c -t, -r \n -T -S "ServerName"'
 


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.