I googled around for this problem, and it appears that "It's supposed to
work that way" is the answer. Is this the only answer, that this class of
error throws a "fatal" & immediately short-circuits further execution of the
remainder of the SP?
This has become a show stopper at the moment.
I'm trying to run through a directory of ASCII files, so having a fatal on
just one file is a poor option when there are 20 files to import. I'd use
xp_cmdshell, but its permissions seem to get turned off whenever a patch is
applied, and that doesn't guard me against a bad 0-byte ASCII file (no
Is there perhaps a way to set up DTS so it handles dynamic file names when
importing from ASCII? Any suggestions?
PS, Just to reiterate from my previous post, I thought of using a linked
OLEDB text server, but that requires the right OLEDB driver and permissions,
along with a capable DBA watching over things (plus if the file doesn't
exist, will the OLEDB driver throw the same kind of fatal?). Using bcp in
DOS from a scheduler doesn't solve the dynamic file name problem (oh yeh,
try programming DOS batch files... it can be done, BUT!!!), and implementing
client software (perl, WSE, etc.) on the server or workstation creates
complexity & other headaches. Please, oh dear God, Why ME!!!!???? Tell them
Previous n.g. thread:
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
> I don't have control of this server & the current admin & users aren't
> technical... As such I want to avoid using xp_cmdshell... similarly, I
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
> have something that works stand alone that can run w/out intervention and
> minimum of admin.