Max size file in BCP process

Question

Max size file in BCP process

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

SATYA R - Thursday, October 4, 2018 6:16 AM
Hi Excerpts ,
I have installed BCP utility in Linux server.
when am executing below command it throwing error, any input to resolve this issue.
/opt/mssql-tools/bin]$ ./bcp test.dbo.ACCOUNT in "u01/bcp/file/ACCOUNT.txt" -T -t, -c -U admin\\admin1-S 10.187.111.99,36686\\admin
SQLState = S1000, NativeError = 851968
Error = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: KEYRING:persistent:23179318)
SQLState = S1000, NativeError = 851968
Error = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context
Thanks,
RR

Spelling note. Excerpts are segments of written material typically extracted from some whole unit of work for the purpose of quotation. Experts are people knowledgeable in a given field of study.

How did you plan on connecting to the server, given the BCP utility. I'm not 100% sure, but I would have thought it would still need the Linux version of SQL Native Client to provide the Kerberos protocol / communication capability. And you have to actually have a functioning network path between the two servers involved. I think I recall saying you couldn't even ping the SQL Server from the Linux server. If that is still true, installing BCP isn't going to be the solution. You have to have network connectivity that is independent of the software installed. If you can't ping, you can't function. Period....

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

SATYA R - Thursday, October 4, 2018 6:16 AM
Hi Excerpts ,
I have installed BCP utility in Linux server.
when am executing below command it throwing error, any input to resolve this issue.
/opt/mssql-tools/bin]$ ./bcp test.dbo.ACCOUNT in "u01/bcp/file/ACCOUNT.txt" -T -t, -c -U admin\\admin1-S 10.187.111.99,36686\\admin
SQLState = S1000, NativeError = 851968
Error = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: KEYRING:persistent:23179318)
SQLState = S1000, NativeError = 851968
Error = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context
Thanks,
RR

I don't even know how to spell "Linux" so I'll have to take my leave now. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SATYA R Right there with Babe Points: 715 More actions · Answer 3

@sgmunson : Apologies for spell mistake.
Yes, I am not able to ping sql server from my Linux box. I will inform to same thing to Network team apart from this anything i need to specify during communication ?
One more thing, If servers talk to each other then problem will resolve ?

sgmunson SSC Guru Points: 110640 More actions · Answer 4

SATYA R - Thursday, October 4, 2018 7:47 AM
@sgmunson : Apologies for spell mistake.
Yes, I am not able to ping sql server from my Linux box. I will inform to same thing to Network team apart from this anything i need to specify during communication ?
One more thing, If servers talk to each other then problem will resolve ?

Talk to your network team. If there is a firewall there, you may run into all manner of grief. Opening ports in a firewall to the DMZ carries a rather high risk. You may not be able to get connectivity without going through extensive network penetration testing. Not sure what ports your existing server is using, but it all has to be configured to work together in order to work at all. You'll have to read up on the Linux version of SQL Server, and whether you'd also need a Linux version of the SQL Native Client, or would have to be sure the Kerberos protocol is either built-in to the BCP program, or if BCP relies on the native client...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

SATYA R - Thursday, October 4, 2018 7:47 AM
@sgmunson : Apologies for spell mistake.
Yes, I am not able to ping sql server from my Linux box. I will inform to same thing to Network team apart from this anything i need to specify during communication ?
One more thing, If servers talk to each other then problem will resolve ?

Have you tried to ping your Linux box from SQL Server? If you can, then do a "pull" instead of a "push".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SATYA R Right there with Babe Points: 715 More actions · Answer 6

HI All,

in file have around 100000 records but command was successful with 0 records. No error described in error log. Any inputs highly appreciated.
./bcp dbo.test in "/test/bcp/bcp_test.dat" -t~~, -c -d test -U admin -S 10.111.555.66,25897 -e /test/bcp/error_log.txt -r "0x0a"
Password:

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 315

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

SATYA R - Friday, October 5, 2018 4:50 AM
HI All,
in file have around 100000 records but command was successful with 0 records. No error described in error log. Any inputs highly appreciated.
./bcp dbo.test in "/test/bcp/bcp_test.dat" -t~~, -c -d test -U admin -S 10.111.555.66,25897 -e /test/bcp/error_log.txt -r "0x0a"
Password:
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 315

Not sure but I think the comma in the server address,port might be the problem. Try encapsulating that whole thing in double quotes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SATYA R Right there with Babe Points: 715 More actions · Answer 8

HI Jeff

I did the change as per your input but no lick still same issue . any one can help ?

./bcp dbo.test in "/test/bcp/bcp_test.dat" -t~~, -c -d test -U admin -S "10.111.555.66,25897" -e /test/bcp/error_log.txt -r "0x0a"

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 8

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

SATYA R - Saturday, October 6, 2018 2:26 AM
HI Jeff
I did the change as per your input but no lick still same issue . any one can help ?
./bcp dbo.test in "/test/bcp/bcp_test.dat" -t~~, -c -d test -U admin -S "10.111.555.66,25897" -e /test/bcp/error_log.txt -r "0x0a"
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 8

Yes... Time to "Divide'n'Conquer". Please see the other thread you started. And please stop creating multiple threads for the same problem. It's not only annoying but it also prevents others from seeing what has already been tried.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SATYA R Right there with Babe Points: 715 More actions · Answer 10

Ok jeff,

I am trying to load same file in my local setup in windows server , now i can see some error in out.log file .i tired get help in google but every one saying that it could be char type issue or filed separator issue. I used char type -c and field separator is ~~ but sill no luck same error. I have extracted data into excel and it was perfectly matching with number of columns from file to target table (264 columns).

bcp test.dbo.ACCOUNT IN D:\bcp\test.dat -T -t~~ -c -r "0x0a" -S admin\SQLEXPRESS -U sa -P xxxx

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

SATYA R - Saturday, October 6, 2018 10:19 PM
Ok jeff,
I am trying to load same file in my local setup in windows server , now i can see some error in out.log file .i tired get help in google but every one saying that it could be char type issue or filed separator issue. I used char type -c and field separator is ~~ but sill no luck same error. I have extracted data into excel and it was perfectly matching with number of columns from file to target table (264 columns).
bcp test.dbo.ACCOUNT IN D:\bcp\test.dat -T -t~~ -c -r "0x0a" -S admin\SQLEXPRESS -U sa -P xxxx
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

The posts about it being a field terminator issue are correct. With the idea of teaching a man to fish, what do you think the "-c" parameter actually does? 😉
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017#c

While you're there, consider what the "-C" (upper case instead of lower case) parameter does and consider using "RAW" in conjunction with it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)