Using special characters in BCP command

Question

Hi.

I have the below query which I am trying to execute from a c# code using the process function.

BCP "SELECT ('"' + replace(ColName, '""', '""""') + '"') as commens
FROM TableName " queryout "FilePath" -T -S ServerName -d DBName -b 500 -c -C 65001 -t~

Since the command contains + , the BCP is failing with error : Copy direction must be either 'in', 'out' or 'format'. I am not able to use QuoteName function for the column, as it is of type varchar(max) and fails with error string or binary data would be truncated.

How to use + in BCP for the query ?
Thanks

Answer 1

You can try something like this:

BCP "SELECT concat('""', replace(ColName, '""', '""""'), '""') as comments…

But for efficiency maybe it is better to show your C# code too. Also reconsider the quotename and char(34) functions (https://learn.microsoft.com/en-us/answers/questions/335100/bcp-error-copy-direction-must-be-in-out-or-format.html).

Answer 2

The plus has nothing do with it, but the double quotes have. They mess everything up for the command line. Instead of '"' use char(34). (I have a nagging feeling that I have told you this before.)