none
Error: Cannot bulk load. Invalid number of columns in the format file

    Question

  • Hi Team,

    I am trying to do a Bulk Insert from a monster data file into a sql server 2005 Table with a format file. Here's the command I use:

    bulk insert AdventureWorks..BulkTestTable from '\\john\data\report.dat' with (formatfile = '\\john\formats\report.fmt',TABLOCK,batchsize=10000)

    The format file is of version 7.0 and it has 1211 columns in the host file. Of which I am trying to insert 882 columns into my table. The Data file i use is contiguous in nature and each column is defined with specific widths. I have checked and rechecked the format file and the data file for leading spaces and whether all the length of data and their positions match and all seem to be fine. Still I am getting this error.

    Msg 4822, Level 16, State 1, Line 1 Cannot bulk load. Invalid number of columns in the format file "\\john\formats\report.fmt"

     I tried out the openrowset(bulk..) functionality but I get the same error "Cannot Bulk Load..invalid number of columns...

    I have also tried bulk insert with an XML format file later but the same error is appearing...i have no idea why this is happening...

     I have only 6 rows in the test data file...and this bulk insert task executes daily once...Out of 1211 columns in the datafile defined by a fixed width format file, there are so many columns that i am leaving out from datafile....where i am placing zero for columns to be ignored.... and i have checked this so many times...still the same error...

    Interesting fact is that, when i try to do the same thing using BCP command line Utility , i am able to load the data file successfully into the same table with the same format file. Its failing only for BULK INSERT...

    I cannot use BCP, as i have to amend the existing setup that is running BULK INSERT

    PLEASE please HELP!!! :-(

     

    Wednesday, December 08, 2010 8:48 AM

Answers

  • I've now made some research, and I can't but draw the conclusion that this is an undocumented implementation restriction.

    I wrote a Perl script that generates the format file and data file with the desired number of columns (of which only the first is imported).

    On SQL 2008 and SQL 2005, the maximum number is 1023 columns. I also ran the test Denali, the next major version of SQL Server currently in beta, and here the limit is higher - 1024 columns!

    I've submitted about a bug on Connect
    https://connect.microsoft.com/SQLServer/feedback/details/630203/cannot-use-bulk-insert-if-format-file-has-more-than-1023-1024-fields

    But to be frank, I expect it at most least to an update of the documentation. It is quite apparent that the behaviour is by design.

    If you want this to be changed, you can try to whip a number of votes. Even better open a case with Microsoft and present solid business reasons why BULK INSERT should support more columns. If you have a really good case, they might supply a hotfix.

    Else, I think you need to bite the bullet and go for BCP. Unless you can collapse the format file to have at most 1023 columns.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Joji John Friday, December 10, 2010 2:41 AM
    Thursday, December 09, 2010 9:03 PM

All replies

  • I don't have the time to resarch the problem right now, but my gut feeling is that you have hit a hard limit. Not the least since it works with BCP.

    Therefore I think you need to to consider a different strategy, for instance use BCP instead. Obviously that means that the task will be a lot bigger than it was initially, since from what you say, you will need to re-architect the application.

    Since you only import about two-thirds of the column, is it possible that you can collapse adjacent non-imported fields? This could reduce the number of fields below 1024, which is my guess for the secret number. (It sounds like an extremely boring piece of work.)

    Do you have access to an instance of SQL 2008 around? Since SQL 2008 upped the number of columns per table, it is possible that BULK INSERT followed suit.

    BCP uses ODBC, whereas BULK INSERT and OPENROWSET (BULK) uses OLE DB, which can explain why they behave differently.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Wednesday, December 08, 2010 11:57 AM
  • If you are willing to go the SSIS way, you can get real good help at the following forum:

    SQL Server Integration Services

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL Server 2008 Training
    Wednesday, December 08, 2010 12:09 PM
  • Thank you so much for your reply, Erland...

    I did try the same with the SQL 2008 Instance...but to no avail...

    As you suggested I tried creating a another table with different schema, this time to load around 1000 columns with the appropriate format file but I am back to square 1...Though we can have 30000 columns in sql 2008, including sparse columns too...this didnt have any difference...as the maximum number of nonsparse columns plus computed columns in a wide table remains 1,024. And I was careful enough to select the columns to be loaded so that they are less than 8000 bytes per row...So loading all columns in data file is not possible as the file has more than 13000 bytes per row....

    And in 2008 too, BCP works fine...Only BULK INSERT fails...!!!

    I tried doing the same thing in SSIS - Bulk Insert Task but I get the same error there too...

    This error was reported earlier in one such msdn discussion forums but i guess NO conclusive answers were provided...

    While BCP is an option, I am still struggling with this and I am left with 2 questions in mind:

    1. Is there any possibility that the data file could be having an issue, albeit BCP Works with the same file?

    2.Could there be any known issues with SQL 2005/2008 itself with regard to this error in Bulk Insert that I am not aware of???

    Please help if you have further ideas...

    Thanks!! John...

    Wednesday, December 08, 2010 5:45 PM
  • Please help if you have further ideas...

    Thanks!! John...


    John,  I had an idea but you ignored it: go SSIS.

    The experts at the SSIS forums are really, really great. The best assistance you can get. And it is free.

    The kind of figures you are mentioning may just be too overwhelming for T-SQL.

    SQL Server Integration Services

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL Server 2008 Training
    Wednesday, December 08, 2010 6:06 PM
  • 1. Is there any possibility that the data file could be having an issue, albeit BCP Works with the same file?

    That's simple to test: just try an empty input file. I would expect that you get the same error.

    2.Could there be any known issues with SQL 2005/2008 itself with regard to this error in Bulk Insert that I am not aware of???


    As I said, I suspect it is a hard limitation.

    I will try to look into the issue more tomorrow night.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Wednesday, December 08, 2010 10:59 PM
  • > John,  I had an idea but you ignored it: go SSIS.

    Kalman, John said he tried he tried a BULK INSERT task in SSIS, and it failed. Maybe there are other ways to do it in SSIS, but it is quite obvious to me that if BCP means more rearchitecturing than John would like, SSIS could be an even bigger step.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Wednesday, December 08, 2010 11:01 PM
  • I tried an empty input file.... and as you exactly said....the same error appeared!!! :-(

    Thanks! John.

    Thursday, December 09, 2010 3:26 AM
  • I've now made some research, and I can't but draw the conclusion that this is an undocumented implementation restriction.

    I wrote a Perl script that generates the format file and data file with the desired number of columns (of which only the first is imported).

    On SQL 2008 and SQL 2005, the maximum number is 1023 columns. I also ran the test Denali, the next major version of SQL Server currently in beta, and here the limit is higher - 1024 columns!

    I've submitted about a bug on Connect
    https://connect.microsoft.com/SQLServer/feedback/details/630203/cannot-use-bulk-insert-if-format-file-has-more-than-1023-1024-fields

    But to be frank, I expect it at most least to an update of the documentation. It is quite apparent that the behaviour is by design.

    If you want this to be changed, you can try to whip a number of votes. Even better open a case with Microsoft and present solid business reasons why BULK INSERT should support more columns. If you have a really good case, they might supply a hotfix.

    Else, I think you need to bite the bullet and go for BCP. Unless you can collapse the format file to have at most 1023 columns.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by Joji John Friday, December 10, 2010 2:41 AM
    Thursday, December 09, 2010 9:03 PM
  • Thanks so much for your help Erland...

    I'll re-architect the application the BCP way...

    Thanks again...John..

    Friday, December 10, 2010 2:41 AM