Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, July 13, 2012 2:25 PM
I am trying to do a bulk insert using SQL 2008 R2. When I try to run the bulk insert I receive the error message, " Cannot bulk load becasue the file XXX could not be read. Operating system error (null)."
This would seem to be a Permissions error. However, another bulk insert which stores the data file and the format file in the same location does not fail.
The Format file was generated using BCP. Once generated I changed the separator from /t to ^. I removed the first field, IDNo as it is an auto incrementing field in the SQL table. But I do not know if I removed it from the format file correctly.
What is wrong with my bulk insert?
Below you will find all the information needed to duplicate this error.
1. Create table statement
2. Bulk Insert statement
3. Bulk Insert format file
4. Data file
5. A screen shot showing two bulk inserts using the same folder for storage. The first one is successful. The second bulk insert, the topic of this post, fails.
Create Table statement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AMRollsInv](
[IDNo] [bigint] IDENTITY(1,1) NOT NULL,
[Preface] [varchar](4) NULL,
[RollNo] [bigint] NULL,
[Loc] [char](3) NULL,
[ItemNo] [varchar](15) NULL,
[ItemFiller] [varchar](15) NULL,
[Status] [char](1) NULL,
[Yards] [decimal](9, 2) NULL,
[Lbs] [decimal](9, 2) NULL,
[ProducedCntry] [char](2) NULL,
[ProducedYYMMDD] [char](6) NULL,
[ProducedDt] [smalldatetime] NULL,
[ProducedDtLong] [char](8) NULL,
[Splice1] [decimal](9, 3) NULL,
[Splice1RollNo] [bigint] NULL,
[Splice2] [decimal](9, 3) NULL,
[Splice2RollNo] [bigint] NULL,
[Splice3] [decimal](9, 3) NULL,
[Splice3RollNo] [bigint] NULL,
[Warp] [char](1) NULL,
[PONo] [varchar](8) NULL,
[PORel] [char](2) NULL,
[PoLine] [int] NULL,
[CusOrdNo] [varchar](8) NULL,
[CusOrdNoLine] [int] NULL,
[DateSoldCntry] [char](2) NULL,
[DateSoldYYMMDD] [varchar](6) NULL,
[DateSoldDt] [smalldatetime] NULL,
[DateSoldDtLong] [char](8) NULL,
[DateLastModCntry] [char](2) NULL,
[DateLastModYYMMDD] [varchar](6) NULL,
[DateLastModDt] [smalldatetime] NULL,
[DateLastModDtLong] [char](8) NULL,
[Comment] [varchar](50) NULL,
[AorN] [char](1) NULL,
[DfltLocYn] [char](1) NULL,
[CutStatus] [char](1) NULL,
[SecondQuality] [char](1) NULL,
[Filler] [varchar](31) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Bulk Insert statement
INSERT INTO AMRollsInv
(Preface,RollNo,Loc,ItemNo,ItemFiller,Status,Yards,Lbs,ProducedCntry,ProducedYYMMDD,ProducedDt,ProducedDtLong,Splice1
,Splice1RollNo,Splice2,Splice2RollNo,Splice3,Splice3RollNo,Warp,PONo,PORel,PoLine,CusOrdNo,CusOrdNoLine,DateSoldCntry,DateSoldYYMMDD,DateSoldDt
,DateSoldDtLong,DateLastModCntry,DateLastModYYMMDD,DateLastModDt,DateLastModDtLong,Comment,AorN,DfltLocYn,CutStatus,SecondQuality,Filler)
SELECT Preface,RollNo,Loc,ItemNo,ItemFiller,Status,Yards,Lbs,ProducedCntry,ProducedYYMMDD,ProducedDt,ProducedDtLong,Splice1
,Splice1RollNo,Splice2,Splice2RollNo,Splice3,Splice3RollNo,Warp,PONo,PORel,PoLine,CusOrdNo,CusOrdNoLine,DateSoldCntry,DateSoldYYMMDD,DateSoldDt
,DateSoldDtLong,DateLastModCntry,DateLastModYYMMDD,DateLastModDt,DateLastModDtLong,Comment,AorN,DfltLocYn,CutStatus,SecondQuality,Filler
FROM OPENROWSET(BULK 'C:\BulkInsert\BIRollsInv.txt', FORMATFILE = 'C:\BulkInsert\AmRollsInvFmt.txt', FirstRow=1) as Z
Go
Bulk Insert Format file
10.0
38
1 SQLCHAR 0 4 "^" 2 Preface SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 21 "^" 3 RollNo ""
3 SQLCHAR 0 3 "^" 4 Loc SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 15 "^" 5 ItemNo SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 15 "^" 6 ItemFiller SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "^" 7 Status SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 41 "^" 8 Yards ""
8 SQLCHAR 0 41 "^" 9 Lbs ""
9 SQLCHAR 0 2 "^" 10 ProducedCntry SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 6 "^" 11 ProducedYYMMDD SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 24 "^" 12 ProducedDt ""
12 SQLCHAR 0 8 "^" 13 ProducedDtLong SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 41 "^" 14 Splice1 ""
14 SQLCHAR 0 21 "^" 15 Splice1RollNo ""
15 SQLCHAR 0 41 "^" 16 Splice2 ""
16 SQLCHAR 0 21 "^" 17 Splice2RollNo ""
17 SQLCHAR 0 41 "^" 18 Splice3 ""
18 SQLCHAR 0 21 "^" 19 Splice3RollNo ""
19 SQLCHAR 0 1 "^" 20 Warp SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 8 "^" 21 PONo SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 2 "^" 22 PORel SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 12 "^" 23 PoLine ""
23 SQLCHAR 0 8 "^" 24 CusOrdNo SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 12 "^" 25 CusOrdNoLine ""
25 SQLCHAR 0 2 "^" 26 DateSoldCntry SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 6 "^" 27 DateSoldYYMMDD SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 24 "^" 28 DateSoldDt ""
28 SQLCHAR 0 8 "^" 29 DateSoldDtLong SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 2 "^" 30 DateLastModCntry SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 6 "^" 31 DateLastModYYMMDD SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 24 "^" 32 DateLastModDt ""
32 SQLCHAR 0 8 "^" 33 DateLastModDtLong SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "^" 34 Comment SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 1 "^" 35 AorN SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 1 "^" 36 DfltLocYn SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 1 "^" 37 CutStatus SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 1 "^" 38 SecondQuality SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 31 "\r\n" 39 Filler SQL_Latin1_General_CP1_CI_AS
Data file (this is a text file but I do not see a way to attach a text file here)
190,^0000019270^NT^G32PAA-50-00^^I^50.00^149.00^19^941024^10/24/1994^19941024^00.000^0^00.000^0^00.000^0^^00000000^00^000^00000000^0000^00^000000^1/1/1990^00000000^20^111219^12/19/2011^20111219^^A^Y^^^
190,^0000019271^NT^G32PAA-50-00^^I^50.00^149.00^19^941024^10/24/1994^19941024^00.000^0^00.000^0^00.000^0^^00000000^00^000^00000000^0000^00^000000^1/1/1990^00000000^20^091016^10/16/2009^20091016^^A^Y^^^
Screen shot of two bulk inserts.
If there is anything else need to assist in resolving this issue, please let me know.
Thanks in advance for your help.
pat
Friday, July 13, 2012 2:55 PM ✅Answered | 2 votes
Found part of the error.
The format file must have a blank line at the end. I had purposely made sure there was NOT a blank line. My previous experience determined that a blank line after the last line of DATA would cause the bulk insert to fail. So I reasoned that the Format file shouldn't have a blank line either. In this instance that assumption is wrong.
The Format file MUST have a blank line at the end.
Oddly, once this insert was working, it would only import the first of the two lines. Addtional fiddling on my part determined that a blank line WAS required after the last line of data. I know that contracts what I stated above. But this is what worked.
Here is a partial explanation.The following applies to just my situation. I have not tested it anywhere else.
The last line of the Format file contains /r/n. When both of these characters are present, the data file must contain a blank line at the end or the last line of data will not be inserted.
If the last line of the format file contains only a /r, then the insert will FAIL if there is a blank line at the end of the data file. Once that blank line is removed at the end of the data file, the insert will not fail. However, it will also not import the last line of data.
Perhaps someone else can explain this behavior more thouroughly.
pat
PS To check if you have permission to these files, try:
exec xp_fileexist 'path to your file'
In my case "path to your file" would be c:\bulkinsert\AmRollsInvfmt.txt
Friday, July 13, 2012 2:50 PM
BULK INSERT runs from the server and service context. Are the files you are trying to load on the server C:\BulkInsert\ or your PC? Also, the SQL Server service account needs to have access to the files.
Friday, July 13, 2012 10:36 PM
To be exact, the last line in the format file must have a terminating \r\n. There does not have to be an empty line at the end.
There is little to defend this behaviour, and the poor error message does not make it better. BCP gives a different error message, but it's hardly any better - it yields about I/O error. (I was battling this problem myself the other day.)
As for the data file, it should comply to the format in the format file. And if the format file says that the last field is terminated with \r\n, the last two characters in the file should be exactly those two.
It helps to think of BULK INSERT and BCP as readers of a binary stream. They consume one field at a time, and gobble up bytes for that field as specifed by the columns 3 to 5 in the format file. They don't think in lines, and if there is a line break when it is looking for a ^ as field terminator, it will consider the \r\n to be part of the data.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se