SQL- BCP Export ORDER BY with UNION ALL

Question

I am trying to do a bcp data export to a CSV file. But I found that the data in my CSV is out of order. So I am trying to add ORDER BY to properly sort in the correct order. I am using UNION ALL to bring in the column headers, however that appears to create issues when trying to use ORDER BY.

bcp "select CHAR(34)+""SampleNo""+CHAR(34),CHAR(34)+""Location Code""+CHAR(34),CHAR(34)+""Location Description""+CHAR(34),CHAR(34)+""Collection Date""+CHAR(34),CHAR(34)+""Submit Date""+CHAR(34),CHAR(34)+""File Name""+CHAR(34),CHAR(34)+""Analysis Code""+CHAR(34),CHAR(34)+""Analyte Name""+CHAR(34),CHAR(34)+""Result""+CHAR(34)+CHAR(44) union all select CHAR(34)+SAMPNO+CHAR(34),CHAR(34)+LOCCODE+CHAR(34),CHAR(34)+LOCDESCR+CHAR(34),CHAR(34)+COLDATE+CHAR(34),CHAR(34)+SUBDATE+CHAR(34),CHAR(34)+MYSAMPNO+CHAR(34),CHAR(34)+ACODE+CHAR(34),CHAR(34)+ANALYTE+CHAR(34),CHAR(34)+RESULT+CHAR(34)+CHAR(44) FROM [TEST].dbo.DATA_EXPORT ORDER BY ""SAMPLE_NO"" " QUERYOUT '+ @file + ' -S SERV2012R2 -U sa -P password -c -t","

I get the following errors:

SQLState = 37000, NativeError = 408
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]A constant 
expression was encountered in the ORDER BY list, position 1.

And...

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column 
level collations
BCP copy out failed

What I am trying to accomplish is that the CSV data follows the same ordering as the database table.

Please format your code a bit better - that's unreadable, also can you be clearer about what the problem is? — Mark Taylor, Sep 18 '19 at 04:26
@MarkTaylor Thanks for the feedback Mark. Made some edits and clarified what I am trying to accomplish. — heejin ghim, Sep 18 '19 at 05:39

score 0 · Answer 1 · answered Sep 18 '19 at 09:32

I figured it out, instead of using the column name I just needed to use the field position. So in this case it was position 1, "ORDER BY 1".

bcp "select CHAR(34)+""SampleNo""+CHAR(34),CHAR(34)+""Location Code""+CHAR(34),CHAR(34)+""Location Description""+CHAR(34),CHAR(34)+""Collection Date""+CHAR(34),CHAR(34)+""Submit Date""+CHAR(34),CHAR(34)+""File Name""+CHAR(34),CHAR(34)+""Analysis Code""+CHAR(34),CHAR(34)+""Analyte Name""+CHAR(34),CHAR(34)+""Result""+CHAR(34)+CHAR(44) union all select CHAR(34)+SAMPNO+CHAR(34),CHAR(34)+LOCCODE+CHAR(34),CHAR(34)+LOCDESCR+CHAR(34),CHAR(34)+COLDATE+CHAR(34),CHAR(34)+SUBDATE+CHAR(34),CHAR(34)+MYSAMPNO+CHAR(34),CHAR(34)+ACODE+CHAR(34),CHAR(34)+ANALYTE+CHAR(34),CHAR(34)+RESULT+CHAR(34)+CHAR(44) FROM [TEST].dbo.DATA_EXPORT ORDER BY 1 " QUERYOUT '+ @file + ' -S SERV2012R2 -U sa -P password -c -t","

Now this has created another problem. Since I am using UNION ALL to generate the header in the file, after apply the ORDER BY 1 clause, the headers are now at the bottom of the file. Wonk wonk wonk...

SQL- BCP Export ORDER BY with UNION ALL

1 Answers1