BCP export limited to MAX_INT rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

BCP export limited to MAX_INT rows?

Author

Topic

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-15 : 09:01:21

someone recently told me that their bcp export of a table with over MAX_INT rows failed because BCP hit an overflow in the internal counter that bcp uses to tell you how many rows it's exported. You know how it write "rows copied to host file: 40000" to stdout? That number got to MAX_INT, then went negative, then BCP crashed. So they had to start all over and break things up with the -F, -L flags.

I was rather surprised that a program that claims to handle "bulk" data would use a 32 bit int to accumulate the count. I suppose the original sybase dev figured nobody would ever want to export more than 2b rows.

just wondering, has anyone seen this before?

I am about to kick off a similar large export and am planning to break it up so as not to hit this, but that also means I won't be able to verify that it is indeed a problem.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-02-15 : 09:20:19

do you have the (disk/time) capacity to create a 'narrow' table with small length columns....and fill same with 2b+ rows? then you can do a home-grown test on the cheap

???

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-15 : 09:32:04

perhaps I'll do that. I already have such a table built so I could just export one bit column from it and see what happens. If I do, i'll report back with the result.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-15 : 14:05:51

Works for me.

1000 rows successfully bulk-copied to host-file. Total received: 1000011000
1000 rows successfully bulk-copied to host-file. Total received: 1000012000
1000 rows successfully bulk-copied to host-file. Total received: 1000013000
1000 rows successfully bulk-copied to host-file. Total received: 1000014000

1000014129 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 3105521

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-15 : 14:11:36

quote:
Originally posted by jhocutt

Works for me.

1000 rows successfully bulk-copied to host-file. Total received: 1000011000
1000 rows successfully bulk-copied to host-file. Total received: 1000012000
1000 rows successfully bulk-copied to host-file. Total received: 1000013000
1000 rows successfully bulk-copied to host-file. Total received: 1000014000

1000014129 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 3105521

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

that's not MAX_INT. max is 2147483647. so you'd need to export at least 2147483648 rows to overflow.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-15 : 14:20:40

Duh, sorry
for some reason, I just went with a billion
Let me try that again

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-15 : 15:57:03

sure enough, it does indeed fail. took me 6 hours to export this many rows of bit columns in native format.

1000 rows successfully bulk-copied to host-file. Total received: 2147480000
1000 rows successfully bulk-copied to host-file. Total received: 2147481000
1000 rows successfully bulk-copied to host-file. Total received: 2147482000
1000 rows successfully bulk-copied to host-file. Total received: 2147483000
1000 rows successfully bulk-copied to host-file. Total received: -2147483296
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Table contains less rows than first row count

BCP copy out failed

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-15 : 15:58:32

:( You beat me, Mine just finished.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-15 : 17:36:37

ok i got to ask... billion rows? what are you saving in there??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-15 : 19:16:51

it's for this: http://www.inrix.com/nas.asp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-15 : 19:29:54

ahh... you're using the short scale version of the billion (10^9 = 1,000,000,000)
over the pond we use the large scale one (10^12 = 1,000,000,000,000)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-15 : 20:59:32

ah. i hope never to have to go that far. the largest table I have to work with is 8 * 10^9 rows.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-02-18 : 11:43:15

Aha....maybe you should now feature on "Mythbusters"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-18 : 11:54:30

why? what's the myth?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-18 : 11:59:21

Have you tried using DTS or SSIS?

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-18 : 12:10:14

quote:
Originally posted by Michael Valentine Jones

Have you tried using DTS or SSIS?

CODO ERGO SUM

no, and i don't plan to.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-19 : 15:14:53

I got bored.
DTS on SQL 2000 stopped at 2,147,483,000. I tried to export 3,000,042,387
so while it does not crash, it also does not give a warning that it did not complete. :(

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-19 : 15:20:22

So, is there a 64-bit version of BCP or DTS that uses a bigint counter

?

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-19 : 16:30:02

no idea. it's a rare enough case that one needs to export that many rows anyway. I will break it up from now on just to be safe. bcp is an excellent tool that I use every day. i can forgive it this little flaw.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-19 : 16:45:40

Did anyone try this with SSIS? Maybe it can do this without an overflow.

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-25 : 22:40:12

just to wrap this up, I created a bug on this on the connect site, and MS responded to say that it's fixed in 2008, and that they won't be fixing it in 2005 since it would require an API level change in SNAC. I'm guessing that since the API is at fault here, it likely exists in SSIS as well.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=329821

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources