question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked ErlandSommarskog answered

Using special characters in BCP command

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

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://docs.microsoft.com/en-us/answers/questions/335100/bcp-error-copy-direction-must-be-in-out-or-format.html).


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried with the concat option. But same error.

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

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 PadmanabhanVenkatesh-6789 ·

Try the next command from command line manually:

bcp "SELECT concat('""', replace(Colname, '""', '""""'), '""') as comments….

Or this:

bcp "SELECT concat(char(34), replace(Colname, char(34), concat(char(34),char(34))), char(34)) as comments....

If it works, but the C# program does not work, then show the C# code.


1 Vote 1 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.