file not found bulk insert - 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

Old Forums

CLOSED - General SQL Server

file not found bulk insert

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2005-12-21 : 11:38:18

okay, so in my residential database, i have a table named 'heritage'

this file has 3 fields, areacode,phone,tl1

i have a text file of adds i need to place into the heritage table. i copied and pasted the bulk insert from the help index and edited it for my need and tried it and now i keep getting an error of:

Server: Msg 4860, Level 16, State 1, Line 1
Could not bulk insert. File 'c:\newpubs.dat' does not exist.

i have the text file on my client, and i am connecting via tcp/ip to the sql server and running this code in query analyser. any recommendations?

BULK INSERT residential..heritage FROM 'c:\newpubs.dat'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2005-12-21 : 11:42:00

Oh, i also tried a bulk insert via BCP and got this:

C:\>bcp residential..heritage in newpubs.dat -c -t , -r /n -S192.168.0.16 -Usa
-Ppass
Warning: -n overrides -c.

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

BCP copy in failed

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-21 : 13:05:27

OK,
the Bulk Insert failed - on file does not exist - cos the file exists on the client machine and not the server hosting the sql - rather use the full network path of the file here.
Even though the code was executed from the client it is still running on the server.

The bcp failed cos of the parameters you passed - that warning message probably needs to be looked at - refer Books Online.

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2005-12-21 : 14:25:21

Perfect, that fixed it. and the bcp error was because i had one to many fields. got it all worked out and it worked perfectly. thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-22 : 02:19:31

Or use \\sysname\DriverName$\FileName

Madhivanan

Failing to plan is Planning to fail

Guttless
Starting Member

2 Posts

Posted - 2007-02-26 : 14:31:33

quote:
Originally posted by albertkohl

Perfect, that fixed it. and the bcp error was because i had one to many fields. got it all worked out and it worked perfectly. thank you.

I am having the same problem you were having with the BCP error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

I have checked my fields on the source and destination tables, same schema. I have no idea what the problem is at this point.

Here are my out and in commands:

bcp "exec IRIS.dbo.QualifyMedClaims" queryout S:\Atlanta\_AE\ExtractClaims_10_tmp.dat -Snameofserver -N -T -k -E

bcp IRIS.dbo.ExtractClaims_10_2 in S:\Atlanta\_AE\ExtractClaims_10_tmp.dat -nameofserver -N -T -k -E

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-27 : 02:25:54

to see what column is failing the insert, use the -e switch on bcp to save an error log file. add this to your cmd line: -eErrors.txt

probably you've got some char-type columns that are not wide enough for the data in your bulk insert file.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources