locked
how to solve issue in exporting data to csv using bcp? RRS feed

  • Question

  • User-1640542475 posted

    Hi

    I have an issue, I could not export data by using bcp command. I am using below code, but csv file cannot be created. below is my code

    declare @sql varchar(8000)
    DECLARE @columnHeader VARCHAR(8000)
    DECLARE @table_name  VARCHAR(50) ='ZR_tbl_Export_FILE'
    DECLARE @Servername varchar(100)='sils-pc\SQLEXPRESS'
    
    
    SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM 
    guard_security.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table_name
    
    SELECT @sql = 'bcp "SELECT '+ @columnHeader +' UNION ALL SELECT * FROM ZR_tbl_Export_FILE" queryout 
          c:\ExportExcel\Export_FILE.csv -c -t, -T -S'+ @servername
    
    exec master..xp_cmdshell @sql

    Pls advice me

    thank you

    Maideen

    Thursday, September 12, 2019 6:26 AM

All replies

  • User-719153870 posted

    Hi maideen5,

    I have an issue, I could not export data by using bcp command

    Have you met any error and got any error message?

    You can print your @sql before the exec to see if you set the @sql right or other parameters.

    Best Regard,

    Yang Shen

    Friday, September 13, 2019 7:38 AM
  • User77042963 posted

    try put your bcp command in one single line:

    declare @sql varchar(8000)
    DECLARE @columnHeader VARCHAR(8000)
    DECLARE @table_name  VARCHAR(50) ='ZR_tbl_Export_FILE'
    DECLARE @Servername varchar(100)='sils-pc\SQLEXPRESS'
    
    
    SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM 
    guard_security.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table_name
    
    SELECT @sql = 'bcp "SELECT '+ @columnHeader +' UNION ALL SELECT * FROM ZR_tbl_Export_FILE" queryout c:\ExportExcel\Export_FILE.csv -c -t, -T -S'+ @servername
    
    exec master..xp_cmdshell @sql

    Monday, September 16, 2019 2:11 PM
  • User753101303 posted

    Hi,

    Seems also you generate -SMyServer rather than -S MyServer. It could be also a permission error (xp_cmdshell not enabled or SQL Server not being allowed to write to this location, also the db is not specified).

    IMO always start from the actual error message rather than trying to guess by reading the code which is often much slower...

    Monday, September 16, 2019 3:12 PM