Bcp queryout issue

  • Hi all,

    I am running the following query in SQL, trying to export some data to a csv file, with header.

    When I try to execute it from SQL server, it exports only the header and not the row data as well.

    When I print it and execute it from cmd, the file contains the header and the row data as well.

    Any ideas what could be the problem?

    declare @getdate-2           as nvarchar(8)

    set           @getdate-2             = (select convert(nvarchar(8),getdate(),112))

    DECLARE @sql_e                    varchar(4000),

    @stm                 varchar(4000),

    @outputDir           varchar(255),

    @CsOutputFile varchar(200)

    set           @sql_e               = 'select ''Field_1'', ''Field_2'', ……., ''Field_n'''

    set           @sql_e               = @sql_e + ' union all select convert(nvarchar(50), Field_1), convert(nvarchar(50), Field_2), …………, convert(nvarchar(50), Field_n)'

    set           @sql_e               = @sql_e + ' from Database.dbo.Table_Name'

    set           @outputDir           = '\\XXX\'

    set           @CsOutputFile = 'File_Name_' + @getdate-2 + '.csv'

    set           @stm                 = 'bcp "' + @sql_e + '" QUERYOUT "' + @outputDir + @CsOutputFile + '" -c -C1253 -t ";" -r \n -S ' + @@SERVERNAME + ' -T'

    --select @stm

    EXEC master..xp_cmdshell @stm

     

  • Security. Your login has different privs than whatever process you're running under through the server.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • So, what kind of privileges should be given?

  • Whatever will make the behavior the same. I can't tell from where I sit.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • @kakovatos7:

    where is the output folder located? The domain user which is running the SQL Server Service needs access to that folder. Ask your NS team to provide access to the same... for read/write/delete/create files.

    =======================================================================

  • @Emperor100

    In this case, I suppose neither the file with just headers would be exported, correct?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply