Bulk insert problem - 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

General SQL Server Forums

New to SQL Server Programming

Bulk insert problem

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-04 : 06:50:07

Hello friends

i am trying to bulk insert a csv into the database.but i was unable to do that. when ever i m trying to do bulk insert i m getting an error like

Msg 4860, Level 16, State 1, Line 2
Cannot bulk load. The file "F:\ZAKEER\Logistics Documents\state.csv" does not exist.

but the path is correct. i m using sql server 2005.

How to rectify this problem and also let me know how to create a format file.

Thanks in advance.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-04 : 07:01:44

Is that path accessible from the database server? if you are connected to a remote machine you must specify a path from that servers location, which is probably not the same as your workstations. I image that the file is sitting on a drive accessable from your computer which is not accessible / got a different drive letter from the database server.

You could use bcp instead.

Open a command prompt and type

bcp <databaseName>..<tableName> IN "F:\ZAKEER\Logistics Documents\state.csv" -S<<name of dbserver> -U<loginName> -P<password>

and then it will take you through some prompts for datatype etc. At the end it will ask you if you want to save a format file.

Hope this helps you out.

Charlie

-------------
Charlie

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-04 : 21:08:05

If it's on remote location, have to use unc name to reference file and ensure sql service account has permission to read it.

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-05 : 01:27:17

Thanks for your prompt reply charlie.....

when i was trying to that i am getting error like

Enter the file storage type of field vCNTNM [char]: char
Enter prefix-length of field vCNTNM [2]: 2
Enter field terminator [none]: none

Enter the file storage type of field cCNTID [char]: char
Enter prefix-length of field cCNTID [2]: 2
Enter field terminator [none]: none

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: hi.fmt

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

actually i am having column datatypes as varchar(50) for vCNTNM and char(2) for cCNTID. but i couldn't find the varchar datatype and even the size .

Please help me..

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-05 : 23:46:13

You set data type to char[2] for vCNTNM in format file, sql copied 0 row but didn't give error.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 04:10:22

Well you are not getting an error from bcp.

errors look like....

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

or similar.

Can you post your table definition and some sample data?

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources