July 17, 2011 at 8:07 pm
I've created a text-file in notepad containing
[font="Courier New"]test1<crlf>
test2<crlf>[/font]
Where <crlf> is a normal press of the enter key
When I try to do a bulk insert from this file, I get the error
[font="Courier New"]
Msg 4832, Level 16, State 1, Line 7
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 7
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 7
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
[/font]
Here's the query it's being used in for reference:
[font="Courier New"]
IF Object_id('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP(TEST INT IDENTITY, LINE VARCHAR(2000))
BULK INSERT #TEMP
FROM 'C:\Test.TXT'
SELECT * FROM #TEMP
IF Object_id('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP[/font]
I doubt the fact that I'm inserting into a temp table would be causing this issue. I've tried adding with rowdelimiter, and setting dataformat to native and textfile, no luck. This seems to be a fairly basic implementation of the command, so I don't understand why it's failing.
July 17, 2011 at 9:02 pm
You have one column of data in your file but you have two columns in your table.
Refer to the follow URL on the BULK INSERT Command.
http://msdn.microsoft.com/en-us/library/ms188365.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 17, 2011 at 9:21 pm
Hi,
Try this and let me know if it works;
,Test1<ctrl>
,Test2<ctrl>
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP
(
TEST INT IDENTITY(1, 1) ,
LINE VARCHAR(2000)
)
BULK INSERT #TEMP FROM 'C:\Test.TXT' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' )
SELECT *
FROM #TEMP
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
July 17, 2011 at 9:26 pm
Your Data File still does not much your Table definition or contain the delimiters specified in your BULK INSERT Command.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply