Export Data to Text File


It is real simple to export data to text file from SQL Server. There are quite few ways that this task can be accomplished.







First we will se how to use the “openrowset”. You can either export data from the output of a “select” statement or from variables as static values with in the proc.

insert openrowset (‘Microsoft.Jet.OLEDB.4.0’, ‘Text;Database=C:\Temp’,filename#txt)

Select col_1 from table_a

insert openrowset (‘Microsoft.Jet.OLEDB.4.0’, ‘Text;Database=C:\Temp’,filename#txt)


The caveat is that you need to have the text file available with the same name exactly in that particular folder with the column headers that you are going to export. This is required because SQL Server open the text file as remote data source.

The second way would be to use the bcp utility within the proc. First let’s see how to implement this.

declare @sql varchar(8000)

SELECT @sql = ‘bcp “select * from talbe_a” queryout c:\temp\filename.txt -c -t, -T -S <servername>’

exec master..xp_cmdshell @sql

By doing this way you do not need the requirement of file existence in that folder as “BCP” creates the file. But wait; there is a caveat in this way too, that the Sql Server instance needs to be set with few options. This can be implemented using Sql Server surface configuration or by using sp_configure proc.

EXEC sp_configure ‘show advanced options’, 1



EXEC sp_configure ‘xp_cmdshell’, 1



The third way would be to create a DTS package using Sql Server Integration Service.

I hope this would help to some degree in getting the data exported to text file.



  1. […] Export Data to Text File […]

  2. san said


    insert openrowset (‘Microsoft.Jet.OLEDB.4.0’, ‘Text;Database=C:\Documents and Settings\sarathn.LGXTST\Desktop’,test#txt)

    Select item, on_hand_qty as onhand from location_inventory where on_hand_qty = ‘5’

    i used this statement . it gives error
    “Server: Msg 213, Level 16, State 5, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.”

    the text contians two header words
    item [tab] onhand

    pls help

  3. san said

    k now its exporting properly in the sql analyzer but if code it in vb it says path is invalid even though it is valid and working in analzer.

    pls revert

  4. shanu said

    How do I export data from sql to text file in ANSI format with | as row delimiter

  5. thiagsundar said

    Hi Shanu,
    You can use anyone of the following bcp command to export to ascii file.

    For pipe separated file then from your sql server management studio run this

    declare @sql varchar(8000)

    SELECT @sql = ‘bcp “select * from [dbname]..[table name]” queryout c:\temp\mytest.txt -c -t’+'”|”‘+’ -T -S [server name]’

    exec master..xp_cmdshell @sql

    For comma separated file then from your sql server management studio run this

    declare @sql varchar(8000)

    SELECT @sql = ‘bcp “select * from [dbname]..[table name]” queryout c:\temp\mytest.txt -c -t, -T -S [server name]’

    exec master..xp_cmdshell @sql

    If you are using copy paste then make sure your quotes within the commands are okay. Otherwise remove then and add it again.

  6. Thanks for writing this article. I used the 2nd/bcp method and it worked great!

  7. ekokoe said

    thanks bro, its worked 🙂

  8. Jeff said

    Howdy… You can avoid enabling the command shell if you can put the BCP command as a job step that uses the Operating System (CmdExec). We have security protocol that prevents us from doing it and this is the workaround

  9. Smithg7 said

    There is visibly a bundle to realize about this. I assume you made certain nice points in features also. aedgecegkbdgddck

  10. sekhar said

    Hi brother,
    while executing below statement im getting below error

    declare @sql varchar(8000)

    SELECT @sql = ‘bcp “select * from MM_DEV_T_D2_P..app_case” queryout E:\files\files\1.txt -c -t’+'”|”‘+’ -T -S @@servername’

    exec master..xp_cmdshell @sql

    —–err msg output

    SQLState = 08001, NativeError = 53
    Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
    SQLState = 08001, NativeError = 53
    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
    SQLState = S1T00, NativeError = 0
    Error = [Microsoft][SQL Native Client]Login timeout expired

    plz help on this.

    • thiagsundar said

      Hi Brother, I think the issue is remote connectivity. Please start the sql server configuration manager (should be under start/microsoft sql server), under network configuration make sure tcp/ip and named pipes connections are enabled. If not then enable and restart sql server service. Also check if sql browser service is running in services.msc. Hope this helps!…

  11. Marina said


    When trying to export the query results to a txt file using the BCP way described here, I get an output as copied below, but the file with the query results does not get created. Otherwise, it says query executed successfully. What’s wrong?

    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]
    [-d database name] [-K application intent] [-l login timeout]

    • thiagsundar said

      Hi Marina,
      If you are running within SSMS then you need to run the following scripts prior as mentioned in this article:

      EXEC sp_configure ‘show advanced options’, 1
      EXEC sp_configure ‘xp_cmdshell’, 1

      This will allow you to run the command shell scripts from SSMS.

      Then you can run the following from SSMS after changing your query, db name (as mentioned mydb in my example), table name (as mentioned xtable in my example), file name, instance name (as mentioned localhost in my example). It works on SQL 2014.

      declare @sql varchar(8000)
      SELECT @sql = ‘bcp “select * from mydb.dbo.xtable” queryout c:\temp\filename.txt -c -t, -T -S localhost’
      exec master..xp_cmdshell @sql

      Still have questions feel free to ask.

  12. Marina said

    Thanks for the reply. I found out the error I was getting was due to new lines. The query worked for me when I typed in the whole bcp command on one line (after the equal sign to be assigned to @sql). When it’s on separate lines, the file doesn’t get created.

  13. Katja said

    Thanks for the description, it is working fine. Is there a possibility to put the string types in ‘xxxx’ (apostrophe) in the output file.

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: