BCP changing header column order

Question

I am trying to run the below query:

BCP "select * from myTable " queryout C:\Temp\myTable.csv -t, -c -T -S

Table:

Process  Verb   Match
P1       V1     FALSE
P2       V2     TRUE

CSV:

Match    Process    Verb
P1       V1         FALSE
P2       V2         TRUE

For some reason, BCP is sorting the header row alphabetically.

The above command used to work absolutely fine in SQL Server 2008 R2, but for some reason it's messing up in SQL Server 2012... How can I fix this?

Answer 1

Perhaps you could try create a query that selects the columns in the order as found in INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, like in the script below:

DECLARE @target_table SYSNAME='myTable';
DECLARE @target_file VARCHAR(1024)='C:\Temp\'+@target_table+'.txt';

DECLARE @cols VARCHAR(8000)=STUFF((
    SELECT 
        ','+QUOTENAME(column_name)
    FROM 
        INFORMATION_SCHEMA.COLUMNS 
    WHERE 
        TABLE_NAME=@target_table
    ORDER BY    
        ORDINAL_POSITION
    FOR XML
        PATH(''), TYPE).value('.[1]','VARCHAR(MAX)'),
    1,1,''
);

DECLARE @bcp_cmd VARCHAR(8000)=
    'BCP ' + 
        '"SELECT '+@cols+' FROM '+QUOTENAME(@target_table)+'" '+
        'QUERYOUT "'+@target_file+'" '+
        '-t, -c -T -S ' + @@SERVERNAME + ' -d ' + DB_NAME();

--SELECT @bcp_cmd;

EXEC xp_cmdshell @bcp_cmd;