How to specify spaced out column names in bcp command?

Question

I am running the following command but i am getting an error:

bcp "select * from (select [Style Code] as [Style Code],[MY Code] as [MY Code] union select [Style Code],[MY Code] from UnPivoted_Table)q order by case [Style Code] when [Style Code] then 0 ELSE 1 END" queryout "\\server.domain.com\CSV_Files\file1.csv" -c -t, -T -S "server1.db.com" -d "DB1"

error:

Starting copy...
SQLState = S0022, NativeError = 207
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'Style Code'.
SQLState = S0022, NativeError = 207
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'MY Code'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

I am following the answer from here because im trying to export the column headers as well to the csv file (otherwise only the data is getting exported if i dont specify the columns in the bcp cmndlet)

I think the error has something to do with the column formatting, because the original answer has non-spaced columns without brackets, but since my columns have spaces in the name, i need to use bracket, but then how do i resolve this error?

score 0 · Answer 1 · answered Jan 08 '21 at 08:44

0

I think you have to set quotes around the column names. To run the query in Management Studio you need single quotes. To run it with bcp you need double single quotes.

bcp "select * from (select ''[Style Code]'' as [Style Code], ''[MY Code]'' as [MY Code] union select [Style Code],[MY Code] from UnPivoted_Table)q order by case [Style Code] when [Style Code] then 0 ELSE 1 END" queryout "\\server.domain.com\CSV_Files\file1.csv" -c -t, -T -S "server1.db.com" -d "DB1"

answered Jan 08 '21 at 08:44

M. Bauer

199
2
9

nope didn't work :( `Starting copy... SQLState = 37000, NativeError = 102 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ''. SQLState = 37000, NativeError = 102 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ')'. SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations` – Cataster Jan 08 '21 at 15:24
check the answer, you were close, instead i had to use single quotes :) – Cataster Jan 08 '21 at 20:14

score 0 · Accepted Answer · answered Jan 08 '21 at 20:13

The right answer is setting single quotes around them, like so

bcp "select * from (select 'Style Code' as [Style Code], 'MY Code' as [MY Code] union select [Style Code], [MY Code] from UnPivoted_Table) q order by case [Style Code] when 'Style Code' then 0 ELSE 1 END" queryout "\\server.domain.com\CSV_Files\file1.csv" -c -t, -T -S "server1.db.com" -d "DB1"

How to specify spaced out column names in bcp command?

2 Answers2