Bulk Insert Does Not Work (Unexpected EOF) But BCP Works

[smedvid]

Help - I can not see the forrest through the trees today!
Most likely a simple issue...

Required to upgrade from SQL Server 2000 to 2008; and need to convert DTS to BULK INSERT since DBA Team does not support SSIS. alternative is to use semi-complex ETL Data-Stage (Unix).

I have a BCP Command Line that works:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp apolyDB.apoly.tblextract_temp IN \\server\Import\val_ext.txt -o c:\testbcp.txt -SDALZ01\APOLYD -Usvc_acct1 -b25000 -t~ -c

And I wanted to convert to Bulk Insert and use an SP to launch the code... I am attempting to run code in Query Windows under SQL Server 2008 as a test:

TRUNCATE TABLE APolyDB.APOLY.tblExtract_TEMP

BULK INSERT  
APolyDB.APOLY.tblExtract_TEMP 
FROM 
'val_ext.txt' 
WITH
( FIELDTERMINATOR = '~',  
BATCHSIZE = 25000, 
ERRORFILE = '\\server\Import\ImportErrors.txt')

Error Message is:
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Note Following Works Loading 1st 1000 rows:

BULK INSERT  
APolyDB.APOLY.tblExtract_TEMP 
FROM 
'val_ext.txt' 
WITH
( FIELDTERMINATOR = '~',  
BATCHSIZE = 25000, 
FIRSTROW = 1,
LASTROW = 1000,
ERRORFILE = '\\server\Import\ImportErrors.txt')

Thanks in Advance....
Steve


Steve Medvid
IT Consultant & Web Master

 

[smedvid]

Bump ---

Update:

Per HEX Review - EOF Appears to have 0D 0A 1A

Text file has a "1A" Hex Character at EOF... Not sure if this is the issue or not...

Any help is a appreciated?

Thanks!



Steve Medvid
IT Consultant & Web Master

 

[gmmastros]

Per HEX Review - EOF Appears to have 0D 0A 1A

As far as I know 0D is Carriage Return and 0A is Line Feed. These things are often used as a row terminator. Speaking of which... I don't see any row terminator in your code, which is a little odd.

Not much help, I'm sure. I suspect the problem has something to do with the CR LF at the end of your file.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

It was an EOF Character that was the issue... File is created in Unix... which has a Hex 1A as EOF.

I guess the Hex 1A Search Helped on Tek-Tips....
Copy command appears to remove the Hex 1A EOF Character.

"Many" Thanks to this member and a solution from Dec-2004! Simple Copy command used to remove Hex 1A.

This is a great site to share the wealth of knowledge....

yksvaan (TechnicalUser) 17 Dec 04 17:26
Could you copy it in windows batch before bulk? Try

copy /Y sourcefile /a resultfile /b

( maybe with master..xp_cmdshell )
and then bulk resultfile. That will get rid of control-Z

end-of-lines might go bonkers, so double-check

Cheers
Backup system is as good as the latest recovery

Steve Medvid
IT Consultant & Web Master

 

[smedvid]

Oooo If anyone could tell me why BCP and DTS worked; I would be curious to increase my knowledge on this issue....

Thanks!



Steve Medvid
IT Consultant & Web Master