question

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

BCP Error - copy direction must be in, out or format

Hi.

I am trying to use the BCP in command line. I am trying for the below code:

bcp "select ('"' + replace(ltrim(rtrim(diary)), '""', '""""') + '"') as diry from test.txt " queryout "path" -t -s servername -d dbname -b 500 -c -c 65001 -t~

I am getting error as - copy direction must be in, out or format

How to fix this

sql-server-general
· 1
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.

This line is causing the error in BCP:

('"' + replace(ltrim(rtrim([columnName])), '""', '""""') + '"')

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

This is starting to get pretty ugly. We need replace CR+LF with nothing (or would you prefer spaces?). But try this:

bcp "select char(34) + replace(replace(replace(ltrim(rtrim(diary)), replicate(char(34), 2), replicate(char(34), 4)), char(13), ''), char(10), '') + char(34) as diry from test.txt"    queryout "path" -T -S servername -d dbname -b 500 -c -C 65001 -t~


And because of the command-line syntax, it has to be a single line.

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


What about replacing single char(34) characters too?

0 Votes 0 ·

When you say it, it makes sense, but Padmanabahn might know something we don't know.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Try this command:

bcp "select ('""' + replace(ltrim(rtrim(diary)), '""', '""""') + '""') as...



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

The result comes with 2 double quotes at the start and end
"" tHIS IS A output ""

I need the output as " tHIS IS A output "

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

Show your last command and make sure that the diary source values do not contain already these quotation marks.

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

Try this:

bcp "select (char(34) + replace(ltrim(rtrim(diary)), replicate(char(34), 2), replicate(char(34), 4) + char(34)) as diry from test.txt " queryout "path" -T -S servername -d dbname -b 500 -c -C 65001 -t~


I have also corrected three switches that incorrectly were lowercase. BCP comes from the Unix world, and the switches are case-sensitive.

· 4
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.

Hi .
Same error.
I did this :
BCP "SELECT (char(34) + replace(ltrim(rtrim([DIARY])), replicate(char(34), 2), replicate(char(34), 4) + char(34))) FROM TABLENAME " queryout "filename" -T -S servername -d table -b 500 -c -C 65001 -t~

The starting text is having double quotes, but the ending text is missing the double quotes. Also, if there are line feeds how to remove that.

I am seeing the repeating pattern as "one space and 2 line feeds" in the data which i receive
Thanks

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog PadmanabhanVenkatesh-6789 ·

I copied and pasted your command into a command-line window, and I got this error message (abbreviated):

SQLState = 08001, NativeError = 53
Error = [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState = 08001, NativeError = 53
Error = [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. 
...
SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

Which is perfectly to be expected since I don't have a machine with that name.

You said that you got the "same error", but you never posted which error you got - but it seemed to be an error with the command syntax.

Please post the exact message you get. Preferably with a screenshot.

0 Votes 0 ·

Apologies for the delay and the wrong response. Thanks for the reply.

The data is getting passed from table to file successfully , but the ending text is missing the double quotes. Also, if there are line feeds how to remove that.

I am seeing the repeating pattern as "one space and 2 line feeds" in the data which i receive, which I want to remove as well.

Now the output I get is : "This is a text

Output required is : "This is a text"

0 Votes 0 ·
Show more comments