Search code examples
sql-serverbcp

How to specify spaced out column names in bcp command?

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?

Solution

  • 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"