BCP IN column limit

I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005? Stated differently, what is the maximum number of columns I can import with bcp? Are there other maximums I should be investigating? My incoming record length is about 2K.

Hi,

I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue? I'd appreciate if you'd share any findings.

Cheers

RichardS71

|||

No success so far. The project has been benched for a while. My current suspect is there is a buffer limit I'm encountering somewhere. but haven't heard or read about one yet. Are you using a straight bcp (bcp with format file), or using the C entry points? We're using the C method right now, and would like to test a straight bcp and see if that eliminates the problem.

Do you have any other ideas?

|||

I'm doing a straight BCP without a format file (using the -c switch to denote all the fields are characters) although I've tried it with a format file too and I'm getting the same problem. For a full description of prob see post with title BCP fails with 134 columns .

I'm also getting the feeling that it's an internal buffer issue. I passed the problem onto a collegue and he did his own example of bcp with 134 fields, and his example worked. His table name and column names were shorter. Think I may be getting closer to the answer. I'll post here if I do.

Regards

RichardS

|||

Ok, I've cracked my problem at least.

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table. You wouldn't believe how long and hard I've been trying to crack this one.

I hope this helps. BTW I've scoured the net and I've not found anything todo with a limitation on number of columns. My inclimation would be to look in another direction, like what you've called your columns , how long the identifiers are, do they start with numbers...that sort of thing.

Tell me how you get on....and good luck.

Regards

RichardS