Bulk Insert - Not returning Errors
-
Hi All,
We are facing one strange problem. The bulk insert command not returning any error though there are errors in script and not inserting data into target tables also.
bulk insert Northwind.dbo.Customers from 'D:\sdfjasdjflasjf.txt'
'D:\sdfjasdjflasjf.txt' - not exist in my dirve. When i execute above statment in Query analyzer i always get
The command(s) completed successfully
If i pass correct file also same message, and not inserting.
Can any one suggest if any settings required to be done? This problem with only this command, remaining all are working fine.
Many thanks in advance.
Soma
-
How do you execute the bulk insert?
When I try it, I get this very clear errormessage:
Server: Msg 4860, Level 16, State 1, Line 1
Could not bulk insert. File 'c:\sdfjasdjflasjf.txt' does not exist.
** edit**
Hmm.. it appears as this error is fatal to the batch and exits immediately. No code after the bulk insert is executed (ie errorchecking and such) since the control is returned to the calling client immediately.
You need to trap this error from where the bulk command is sent.
/Kenneth
-
Hi,
That is the problem i am facing, in my server even i am not getting that error also.
To get that error what settings i need to done.
Soma
-
Unfortunately there is no way in Transact SQL to trap this (or any other fatal) error, since the SQL code stops executing immediately and exits. You never get to the errorhanldling part. There is no settings to control this behaviour.
Your best bet in this case would be to grab the filname and path programatically, then you'd be pretty sure that the file exists, and the error will be avoided.
some methods could be to use xp_cmdshell 'dir myPath\myFile', or xp_fileexists 'myPath\myFile'. Note however that xp_fileexist is undocumented (yet handy
)/Kenneth
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply