sql - Bulk load: An unexpected end of file was encountered in the data file -

sql - Bulk load: An unexpected end of file was encountered in the data file -

i using sql server express 2008

when i'm trying load info txt file in table

create table clients ( clientid int not null identity (9000,1), lastname varchar (30)not null, firsname varchar (30)not null, midinitial varchar (3), dob date not null, adress varchar (40) not null, adress2 varchar (10), city varchar (40) not null, zip int not null, phone varchar (30) , categcode varchar (2) not null, statusid int not null, hispanic binary default 0, ethncode varchar(3) , langid int, clientproxy varchar (200), parent varchar (40), hshldsize int default 1, annualhshldincome int, monthlyyearly varchar(7) , pfds int, wic binary default 0, medicaid binary default 0, atap binary default 0, foodstamps binary default 0, agencyid int not null, routid int , deliverynotes varchar (200), recertificationdate date not null, notes text, primary key (clientid) );

i utilize

set identity_insert clients2 on; mass insert clients2 'c:\sample_clients.txt' ( fieldterminator = ',', rowterminator = '\r\n' )

sql server express trows me errors

msg 545, level 16, state 1, line 2 explicit value must specified identity column in table 'clients' either when identity_insert set on or when replication user inserting not replication identity column.

file has 1 line (for sample data) check many times 1 line

data looks

13144,vasya,pupkin,,1944-10-20,p.o. box 52,,wrna,99909,(907) 111-1111,sr,4,0,w,1,,,3,1198,month,0,0,1,0,1,45,,,2011-04-27

any ideas how prepare problem?

you need parameter keepidentity in mass insert statement. required retain identity values in load.

bulk insert clients2 'c:\sample_clients.txt' ( keepidentity, fieldterminator = ',', rowterminator = '\r\n' )

i think have problem because have no info or placeholder notes column. comma added end of file should address this.

sql database sql-server-2008 sql-server-2008-express loaddata