Bulk Insert Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert Error

Author

Topic

dougancil2009
Starting Member

17 Posts

Posted - 2010-04-23 : 14:22:01

I'm importing a txt file to a sql server and when I try to do a bulk insert I get the error:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

BULK INSERT [dialerresults] FROM [C:\IVR Test\csvtest.txt] WITH (FIELDTERMINATOR = ',' ROWTERMINATOR = '/n')

and here is the sample data:

1,512 206 1293,rob stevens,doctor alexander,1 5 10,12:30, null
2,512 555 1212,jason reed,doctor smith,5 5 10,1:00, null
3,512 444 1111,sean jones,doctor paul,6 1 10,4:00, null
4,512 111 1212,zach cochran,doctor stevens,5 6 10,3:15, null

The table has the following fields

ID (int)

phonenumber (varchar 20)

patientname (varchar 20)

drname (varchar 30)

apptdate (datetime 8)

appttime (smalldatetime 4)

currentdate (smalldatetime 4)

result (char 10)

Also a few things I'd like to fix are:

1. The ID field. I want that to auto populate (Field 1 in the table) which is also the primary key.

2. Current date, again I want that to auto populate as to when the file is uploaded to the server.

Can anyone offer any kind of guidance?

Thank you

Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:35:58

try using OPENROWSET

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil2009
Starting Member

17 Posts

Posted - 2010-04-23 : 14:40:54

I'm sorry but where should I put openrowset?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:42:43

see

http://msdn.microsoft.com/en-us/library/ms190312.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil2009
Starting Member

17 Posts

Posted - 2010-04-23 : 15:01:54

OPENROWSET seems (to me at least) that it is used primarily to connect to remote servers but the file I'm using
is on the SQL server. The errors that I'm getting is when I run the query analyzer on the sql server. Do you think that using OPENROWSET to access local files will fix the EOF error that I'm receiving?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-26 : 04:29:01

Open the file. Press CTRL+END
IF the cursor is not at a new line create a new one

Madhivanan

Failing to plan is Planning to fail

dougancil2009
Starting Member

17 Posts

Posted - 2010-04-26 : 11:28:28

Madhivanan,

Thank you so much for that. It seems that when I created the test file that there were blank lines at the end of the data causing my EOF issues. I had no idea that I could get an EOF error from that. Now I know.

dougancil2009
Starting Member

17 Posts

Posted - 2010-04-26 : 16:46:45

I have modified the sql query so that I'm not seeing any errors now but I am also not seeing the data being inserted into the database. I run a select * from dialerresults query after the insert to see if there is any data there, and there's nothing. Anyone have any ideas or suggestions as to what I may be doing wrong?

Thank you

Doug

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 02:48:55

When you ran bulk insert what was the result?

Madhivanan

Failing to plan is Planning to fail

dougancil2009
Starting Member

17 Posts

Posted - 2010-04-28 : 10:22:29

When I run the bulk insert from the Query Analyser, I get nothing. It says O rows affected and when I run the Select * from dialerresults, I see no data inserted into the table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-28 : 10:28:23

quote:
Originally posted by dougancil2009

When I run the bulk insert from the Query Analyser, I get nothing. It says O rows affected and when I run the Select * from dialerresults, I see no data inserted into the table.

It means you dont have data in the text file

Madhivanan

Failing to plan is Planning to fail

dougancil2009
Starting Member

17 Posts

Posted - 2010-04-28 : 11:01:04

Madhivanan,

I have data in the data file otherwise it wouldn't have given me the errors it was giving me before. The sample data is as follows (and this is a cut and paste from the .txt file)

,512 206 1293,rob stevens,doctor alexander,April 5 10 17:15:00,
,512 555 1212,jason reed,doctor smith,May 5 10 15:00:00,
,512 444 1111,sean jones,doctor paul,May 1 10 13:15:00,
,512 111 1212,zach cochran,doctor stevens,May 6 10 12:30:00,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources