Does anyone know if there's a hard limit to the number of columns you can
import with BULK INSERT? Maybe 255? Any change between SQL 7 and current?

Trying for 335, I get an error with what I believe is a correct fmt file;
says "invalid column number", but doesn't say which.

Thanks,

D

Dave,

I believe the limit is 1024, same as the maximum number of columns
per table. I have used format files with many more columns than 335.

The this error message is most likely accurate. Make sure you have
not repeated a column number in the first field. This is a popular
copy and paste error.

Sometimes it helps to open a format file is Excel to check the line
numbering. Another trick I use to check accuracy is to create a new
format file with bcp and compare it to the offending one to find
descrepancies in the numbering.

Linda

Thanks for the info; it made me look more closely at the fmt file. It had
originally been generated mechanically, so I thought it was almost certainly
correct. However, I'd tweazed it a bit with find and replace, and had
monkeyed it up a bit. Once I fixed those errors it worked fine.

Next problem is that it's timing out when I run this code from enterprise
mgr with the full data I need to import (22 Megs or so). I haven't run into
this issue before for some reason, working primarily via ColdFusion,
including with very large imports. ColdFusion must be managing this for me,
but it doesn't apply here. How can I change the timeout setting, ideally for
this request only, but globally if that's not possible? How do I find out
the current setting to restore it when I'm done?

Thanks again,

Dave


"lindawie" <lindawie (AT) my-deja (DOT) com> wrote

Dave Merrill,

You can change the query timeout setting in the Server Property
dialog, Connections tab. However, Enterprise Manager is not a good
tool for this sort of thing. Use Query Analyzer to execute code
instead.

Linda

Thanks figured that out, not problem. Couldn't find a relevant timeout
setting in enterprise; I looked before writing. No biggie though, fine in
analyzer.

Dave