Bulk Insert w/ .fmt file: Operating system error code (null)

13,952

The .fmt file needs a blank line at the very end.

Share:
13,952
PowerUser
Author by

PowerUser

I do what I can with MS Office, VBA, and as of late a couple of SQL Server queries. There's also a Crystal Reports 2008 certification around here somewhere.

Updated on June 03, 2022

Comments

  • PowerUser
    PowerUser about 2 years

    I'm trying to import a text file defined by a .fmt format. Instead of being comma-delimited, this text file is what I would call column-delimited (i.e. the first 8 characters is the first field, the next 3 characters is the second, etc). When I run the query below, I get a "Operating system error code (null)" message, which is odd since I'm using SQL Server 2008r2 with Vista.

    Please explain to me what this error message means and how do I get around it? I've googled it and found similar questions on other forums, but they're never really answered.

    Here's my query:

    BULK INSERT LoadTable FROM '\\Dev2\Queries\Test.txt'
    WITH (FIRSTROW = 2,
          FORMATFILE = '\\Dev2\fmt\Test.fmt',
          KEEPNULLS)
    

    Here's the error I get:

    Cannot bulk load because the file "\\Dev2\fmt\test.fmt" could not be read. 
    Operating system error code (null).
    

    Here's what Test.fmt contains:

    9.0
    7
    1   SQLCHAR 0   8   ""  1   Record_Control_Data SQL_Latin1_General_CP1_CI_AS
    2   SQLCHAR 0   3   ""  2   Filler  ""
    3   SQLCHAR 0   1   ""  3   Member_Code SQL_Latin1_General_CP1_CI_AS
    4   SQLCHAR 0   2   ""  4   Member_Sequence_Number  SQL_Latin1_General_CP1_CI_AS
    5   SQLCHAR 0   9   ""  5   Participant_SSN SQL_Latin1_General_CP1_CI_AS
    6   SQLCHAR 0   20  ""  6   LastName    SQL_Latin1_General_CP1_CI_AS
    7   SQLCHAR 0   250 "\r\n"  7   Filler10    SQL_Latin1_General_CP1_CI_AS
    

    (This test.fmt and test.txt is a simplified version of a dataset with 120 columns. But the error message is the same. Once I can get this test version running, I'll apply the fix to the real data)

    Edit

    In response to the other half-answers around the web, I should have complete read/write permission to these folders & files.

  • Sam White
    Sam White almost 6 years
    Thank you! Although the logic here is beyond me.
  • PixelPaul
    PixelPaul almost 5 years
    Wow, this was a savior!