locked
export data from sql server 2005 table to text file RRS feed

  • Question

  •  

    Hii

    I want to transfer data from table to a text file.I m trying to use bcp utility and xp_cmdshell.but the export is not successful.

    My query is:

    EXEC master..xp_cmdshell'bcp "Select * from test..emp" queryout "c:\dept.txt" -c -T -x'

     and its output is:

     

    NULL
    Starting copy...
    NULL
    3 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 16     Average : (187.50 rows per sec.)
    NULL

     

    but there is no row copied into c:\dept.txt

     

    where is the problem??

     

    Thanx

    -Supriya

    Wednesday, February 27, 2008 6:03 AM

Answers

  • BCP always overwrites the file if it is already there otherwise create the new file, but There is no option to append.

    But you can achieve this by batch file (BAT)..

     

    The idea is something like this,

    -       Have one file as your main target file, but don’t export data directly to this

    -       Export the data to one temp file

    -       Use the following command type c:\tempexportedfile.txt >> c:\MainExportFile.txt. Here >> is used for appending the files in command prompt.

     

    Now for every export the data will be copied in the tempexportedfile from there it will be appended to MainExportFile.

     

    The SQL Script:

     

    Exec master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\tempexportfile.txt" -c -T -SYourServerName'

    Exec master..xp_cmdshell 'type "c:\tempexportfile.txt" >> "c:\mainexportfile.txt"

     

    Thursday, February 28, 2008 5:41 AM

All replies

  • You have to say the Target File name as of now it is missing in your query..

    master..xp_cmdshell 'bcp "select name from sysobjects" queryout "targetfile.txt" -c -T -SYourServerName'

     

    Wednesday, February 27, 2008 6:45 AM
  • Hi Manivannan

    Thanx for ur reply.

    But I believe that I m specifying the name of output file in my query as been highlighted below:

    EXEC master..xp_cmdshell'bcp "Select * from test..emp" queryout "c:\dept.txt" -c -T -x'

     

    i exceuted the query sent by u as:

     

    master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\text.txt" -c -T -SInt'

     

     but still i didnt get the required export to text file.The error i m getting is:

     

    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
    NULL

     

    Plz help me out.

     

    Thanx

    -Supriya

    Wednesday, February 27, 2008 6:53 AM
  • Well. Yep.. I didn't read properly

     

    Is the trusted user have write permission on the C:, try to give the full access for specific folder ( for current user) & try again. It seams to be a permission issue.

     

    Execute the same BCP command from the command prompt. If it works fine then the issue is related to the folder access permission only.

    Wednesday, February 27, 2008 7:00 AM
  • Hi Manivannan

     

    Ya u r right.When i fired the bcp command from command prompt I successfully got the output into my text file.

    Thanx.But not able to get it thru SSMS.

    I checked the properties of my text file and I granted the windows user full control over the file.But it doesnot help out.

    Moreover i want the column name in the output file as well.

    Also one more thing: what if i want to append the data to output file??

     

    Thanx

    Supriya

     

    Wednesday, February 27, 2008 8:53 AM
  • Hi

    Is there no way to append the data to a text file with column names while exporting from sql server??

     

    -Supriya

     

    Thursday, February 28, 2008 5:14 AM
  • BCP always overwrites the file if it is already there otherwise create the new file, but There is no option to append.

    But you can achieve this by batch file (BAT)..

     

    The idea is something like this,

    -       Have one file as your main target file, but don’t export data directly to this

    -       Export the data to one temp file

    -       Use the following command type c:\tempexportedfile.txt >> c:\MainExportFile.txt. Here >> is used for appending the files in command prompt.

     

    Now for every export the data will be copied in the tempexportedfile from there it will be appended to MainExportFile.

     

    The SQL Script:

     

    Exec master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\tempexportfile.txt" -c -T -SYourServerName'

    Exec master..xp_cmdshell 'type "c:\tempexportfile.txt" >> "c:\mainexportfile.txt"

     

    Thursday, February 28, 2008 5:41 AM
  • To get it thru SSMS :

     

    Check which Service Account used to execute the SSIS package & give the full access on the target export folder for this service account.

     

    Thursday, February 28, 2008 5:42 AM
  • Hi Mani

    Thanx a lot..I am done with it.

    I got it executed both ways through command prompt and SQL command as well.The problem was with issues.After I granted the Windows user  full permission on the text files my query executed correctly.

    Thanx once again

    Supriya

     

    Friday, February 29, 2008 6:17 AM
    • Edited by Kalman Toth Thursday, November 9, 2017 1:59 PM
    Thursday, March 27, 2008 4:53 PM
  •  

    Exec master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\tempexportfile.txt" -c -T -SYourServerName'

     this command works

    But when i give my user defined tables it shows invalid object.

     

    Exec master..xp_cmdshell 'bcp "select name from tblemployee " queryout "c:\tempexportfile.txt" -c -T -SMyserverName'

    it gives the output as

     

    SQLState = 42S02, NativeError = 208
    Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name tblemployee.
    SQLState = 42000, NativeError = 8180
    Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
    NULL

     

     

    Do i need to mention database name  too .I am running this command on sqlserver 2005 sp 2 client

    Thursday, May 29, 2008 2:00 PM
  •  

    Hello,

    thanks for the below information

    master..xp_cmdshell 'bcp "select name from sysobjects" queryout "targetfile.txt" -c -T -SYourServerName'

     

    Text file is tab delimited and stored in server. I want to change it to comma delimited or other format. Can you please tell me how to change it

     

    thanks
    • Proposed as answer by J.S.Virk. _ Friday, August 21, 2009 10:43 AM
    Wednesday, June 4, 2008 10:36 PM
  • master..xp_cmdshell 'bcp "select name from sysobjects" queryout "targetfile.txt" -c -t -T -S YourServerName'

    This will create a comma seperated file instead of tab seperated.
    Friday, August 21, 2009 10:46 AM
  • master..xp_cmdshell 'bcp "select name from sysobjects" queryout "targetfile.txt" -c -t, -T -S YourServerName'

    This will create a comma seperated file instead of tab seperated.

    sorry i missed the comma earlier.

    Enjoy coding
    Friday, August 21, 2009 10:48 AM
  • As part of the original issue. I've found that writing to the root of drives in general is a permission issue. You may want to create a sub folder first. I had the same issue and it worked for me w/o permission changes.

    Sunday, September 20, 2009 1:41 AM
  • . I've found that writing to the root of drives in general is a permission issue. You may want to create a sub folder first.


    Irregard to permission, it is not a good idea to write to the root of a drive. Yes, creating folder or folder structure is the way to go.
    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    • Edited by Kalman Toth Thursday, November 9, 2017 2:00 PM
    Sunday, September 20, 2009 2:26 AM
  • yes you need to mention the database and the schema from where you want to export data as follows

    Exec master..xp_cmdshell 'bcp "select name from Databasename.schemaName.tblemployee " queryout "c:\tempexportfile.txt" -c -T -SMyserverName'

     

    if you haven't create an sxplicit schema in your database, the defaul is dbo

    eg.

    Exec xp_cmdshell 'bcp "SELECT * FROM Test.dbo.tbl_employee" queryout "C:\employee.txt" -T -c -t,'

     

    this will create a coma separated txt file

     

    Thursday, May 6, 2010 10:55 AM
  • Hi,

    I am also facing problem while doing the bcp. Please let me know the steps you performed.

     

    Thanks

    Manoj

    Monday, May 9, 2011 5:31 AM
  • How to use SSIS to export DB to a text file? IS this complicated than SQL command?

     

    I have SQL query written for exporting DB to a text file then someone needs grab the text file to place in another file server for a special application.

     

    I created a data flow task. ---create an OLED source then a flat file destination but it didn't work , got some errors, cannot attach a word doc in this discussion forum.

    Monday, June 27, 2011 6:25 PM
  • Hi,

    In the above script how do I get the export file as '|' delimited instead of tab delimited

     

    Friday, October 14, 2011 3:14 PM
  • now i want same code.................

    export a data from server table to .txt file

    my server is microsoft SQL server 2005

    data moves to DData.txt from table by using a botton click

    Monday, February 18, 2013 5:51 AM
  • Here is how you do it with bcp:

    http://www.codeproject.com/Questions/242589/sql-bcp-export-to-csv-file

    Make sure the command is on one (long) line.


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Edited by Kalman Toth Thursday, November 9, 2017 2:00 PM
    Monday, February 18, 2013 8:05 AM