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

I figured it out myself. It wasn't BCP, it was Syscolumns table in SQl which was causing this.

I was reading the column headers from sys.syscolumns. While in Sql 2008 R2, id field in sys.syscolumns is unique for each column of table, it gives the same values in Sql 2014.

So, when I run below query in Sql 2008 & 2014 separately

SELECT distinct name,
          ROW_NUMBER() OVER ( ORDER BY id) AS RowNumber FROM    sys.syscolumns 
   WHERE   id = OBJECT_ID('MyDB.dbo.MyTbl')

In Sql 2008, it gives the data ordered by id which is unique for each column. However in Sql 2014, since id is same for all the columns, it sorts the results alphabetically.

Now, I changed my query to

SELECT distinct name,
     ROW_NUMBER() OVER ( ORDER BY colid) AS RowNumber FROM sys.syscolumns
WHERE id = OBJECT_ID('MyDB.dbo.MyTbl')