Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Audit with SQL Compliance Manager and SQL Secure

How to Roll your Own Value, RegEx and SoundEx Pattern Profiler in SSIS

Are You Making the Right Choices for SQL Server HA?

SQL Server Security Essentials

Implementing a SANLess SQL Server Cluster in Under an Hour

Simple way to export SQL Server data to Text Files

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

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.

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
EXEC master.dbo.sp_configure 'xp_cmdshell', 1

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

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Thursday, March 12, 2015 - 8:57:45 AM - Carlos Read The Tip
Tip Comments Pending Approval

Saturday, October 11, 2014 - 10:56:04 AM - akshay Read The Tip

Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]


this is the output when i run this query


EXEC xp_cmdshell 'bcp "SELECT * FROM " dbo.data1 "d:\bcptest.txt" -T -c -t,'


but not copying data to text file


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

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


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


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

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

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?



Sorry for my english i speak spanish.


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.

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


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.

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





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.

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.

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.

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


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.



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.

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 Ç

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

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

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

Sort of works with SQL 2005 and SQL 2008

Tuesday, December 09, 2008 - 1:09:23 AM - mrhappi Read The Tip


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

Sponsor Information