Bulk Insert w/ .fmt file: Operating system error code (null)
The .fmt
file needs a blank line at the very end.
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, 2022Comments
-
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 almost 6 yearsThank you! Although the logic here is beyond me.
-
PixelPaul almost 5 yearsWow, this was a savior!