BCP column limit

Question

Post reply

BCP column limit

SATYA R

Right there with Babe

Points: 715
More actions
October 6, 2018 at 7:44 am

#369131

Hi Gurus,
I am trying to load data from file to mssql table using BCP command. In file contains 264 couumns. Is there any restriction to load this kind of file through bcp (MSSQL 2016)? what is the maximum number of columns I can import with bcp?
Thanks
RR

Viewing 12 posts - 1 through 11 (of 11 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

There isn't a limit no, but with that many columns you may reach the limit of SQL Server for the maximum size per row.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

Thom, how to set maximize size row limit? Is there any command to check row size limit ?

Thom A SSC Guru Points: 99130 More actions · Answer 3

You don't set it, it's a limitation. The maximum row size within SQL Server is 8060 bytes. Although you might have more per row if data for a varchar is stored off row. The documentation covers this better than I.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

Why am asking, for testing purpose I have kept one record but it was successful with zero records. Do u ve any idea ? Below is the command
./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

Thom A SSC Guru Points: 99130 More actions · Answer 5

Anything in the error file?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

No error, it was really strange ? Is there any chance exceeded row size limit will encounter this kind of issue ?

Can we check how much row size is occupying during the load orproce ?

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

SATYA R - Saturday, October 6, 2018 9:32 AM
No error, it was really strange ? Is there any chance exceeded row size limit will encounter this kind of issue ?
Can we check how much row size is occupying during the load orproce ?

IIRC, you're also battling with being on a Linux box and trying to push data to SQL Server. You also copied BCP to the Linux server. To help figure out what the problem mechanism is (is it the file, Linux, or what?), you should try to import the file using SQL Server pulling from a Windows directory to see if that can be made to work. That way, you're only fighting one problem at a time.

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

Thom A SSC Guru Points: 99130 More actions · Answer 8

Jeff Moden - Saturday, October 6, 2018 9:38 AM
SATYA R - Saturday, October 6, 2018 9:32 AM
No error, it was really strange ? Is there any chance exceeded row size limit will encounter this kind of issue ?
Can we check how much row size is occupying during the load orproce ?
IIRC, you're also battling with being on a Linux box and trying to push data to SQL Server. You also copied BCP to the Linux server. To help figure out what the mechanism is (is it the file, Linux, or what?), you should try to import the file using SQL Server pulling from a Windows directory to see if that can be made to work. That way, you're only fighting one problem at a time.

Ahh, that would explain the / for the directory path, I was about to post to say they're wrong.

I've not used BCP on Linux, although I do have it installed but not in a Linux to Windows set up. Perhaps the problem is the permissions of the account.

Jeff gives some good advice though; get the process working on the same environment first, and then start adding variables like other operating systems.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

As per your suggesstion , i have done this testing in local server env.

C:\Users\test>bcp test.dbo.AUDIT in "D:\BCP\TEST.txt" -T -t$$ -c -r "0x0a" -S ADMIN\SQLEXPRESS -U sa -P XXXX

Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000

9209 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 62 Average : (148532.27 rows per sec.)
With Large file , am trying to insert but no luck it is throwing same error in windows sytesm as well.

C:\Users\test>bcp test.dbo.ACCOUNT in "D:\BCP\bcp_account.txt" -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

Any one can help highly appreciate.

Note : I have tried using format file as well but no luck still same issue.
command :-

bcp test.dbo.ACCOUNT in D:\BCP\bcp_account.txt -f D:\bcp\acc.fmt -t~~ -c -r "0x0a" -f D:\bcp\account.fmt -S admin\SQLEXPRESS -U sa -P xxx

Thom A SSC Guru Points: 99130 More actions · Answer 10

You have an error there at least, unexpected EOF (End Of File), so it's not quite the same.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

All of this is precisely why it was a grievous error to start a second post on this, Satya. Please see my response on your other post. It will fix your problem. I've also provided a link there so that you can actually see what the real meaning of the "-c" parameter is and your thought about what it does, as expressed on the other post, is only partially correct.
https://www.sqlservercentral.com/Forums/FindPost1999209.aspx

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