July 12, 2012 at 1:29 pm
I have been trying to bulk insert a file into SQL Server 2008 R2. In another post here I created a much smaller file and with help was able to get that bulk import to function. With that experience under my belt I moved on to the larger and more complex table.
The Full error message can be seen in the attachment. After a Google search, I verified that the two file paths and names were correct by copying/pasting into a RUN command. This opened both files. As I mentioned I successfully performed a bulk insert on a differnt table. Its format and data files were in the same folder as I am using here. I am logged in as the same user. As suggested in the other forum post, I used BCP to generate the format file. I changed the seperator from /t to ^. I deleted the IDNo field and adjusted the field numbers on the left.
Differences between my successful insert and this new one.
1. More fields
2. The SQL table's first field is an Autoincrementing field. I tried modify that Format file so that it was not part of the insert. Did I do that correctly? But I do not see how that could tie to this error message.
I have attached the Format and data file (with reduced record count 2 vs 300,000 records).
A Create table statement for the SQL table is listed below.
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
What do I need to do to eliminate this error message?
Thanks,
pat
July 12, 2012 at 3:43 pm
Try using BCP In from the command line using code similar to this.
bcp tblMyTable IN myfile.bcp -S MyServer -f MyFormatFile.XML -h "TABLOCK"
July 12, 2012 at 3:55 pm
Looks like a permissions issue to me. It's not telling you the file doesn't exist, it's saying it can't be read. That either means it doesn't have permission, the file is not a text file (or is in a bad format), or you have the format incorrect. How long does it take before the message pops up? If it's instantly, then it's probably a permissions issue. Keep in mind, in your first example, the user accessing the file is YOU, but when you do the bulk insert it could be another user, whatever user the server instance belongs to - possibly "NETWORK SERVICE" or something. If that user doesn't have permission to read your file, the server won't have permission either.
Also... I see 38 columns in your format file but only 36 in the data. This could cause a read error, but I'm pretty sure the error message for choking on a file during reading is different.
July 12, 2012 at 10:33 pm
Jasmine D.
Thanks for the reply. To test the Permission theory, I ran my smaller bulk insert and the bulk insert that is the topic of this post from the same QUERY window, one after another. As you can see from the attached image the first insert completed and the second did not. The necessary files, data and format, are located in the same directory for both Inserts. This indicates that it is not a folder permission problem. And as I mentioned I am logged into the server with the same name I always use and I am logged into SSMS with the same login I always use.
With regards you comment about the number of fields. I opened the Design table view. I counted 39 fields. Less the IDNo field gives me 38. I then copied the field names from the Data portion of the Bulk Insert (the first occurance of the field names). I saved them as a CSV. I did the same thing with second set of field names in the Bulk Insert. I opened both CSV files in Excel, Transposed the column data so that the field names were located in a single column rather than a single row. When I compare the two sets of field names, I get 38 in both. See attached Excel sheet.
So I am still stuck.
Thanks,
pat
July 13, 2012 at 8:58 am
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
July 13, 2012 at 9:23 am
OK I'm glad you got it working, but please keep in mind that Windows 7 has file-level permissions as well. When you create a folder and set up some access for it, then you create new files in that folder, sometimes those new files don't get the proper permissions assigned from the folder level. Just something to keep an eye out for because it has bit me in the past.
The bug you describe is a bug in my opinion, but it's understandable based on the way it parses through the file. It doesn't see it as a set of lines, it sees a continuous bitstream for the whole file, so it defines a line as either from the beginning of the file to the "backslash-r backslash-n" or from the 'cursor' to the next "backslash-r backslash-n" during reading. So, if your line doesn't end with "backslash-r backslash-n" which is "carriage return and line feed" then that "line" essentially doesn't exist. If you put a blank line after that, then you have inserted a line feed, so then it works. But, if you don't have that blank line then it reads until the file ends.
July 13, 2012 at 9:40 am
Thanks for the additional information.
Do you think if the file is found by the stored procedure that the SQL user would also have access to the file? I do not fully understand file level permissions.
July 13, 2012 at 9:57 am
mpdillon (7/13/2012)
Thanks for the additional information.Do you think if the file is found by the stored procedure that the SQL user would also have access to the file? I do not fully understand file level permissions.
Possibly not, and we had that problem with our reporting system a while back. The system would write a file after the user ran the report, and it would have permission for the file, but the user couldn't view the report because they didn't have access to the file. When the server does something, it's a different user than the user using the server. Does that make sense?
July 13, 2012 at 10:57 am
I will keep that in mind.
thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply