BULK INSERT crashes Stored Procedure ?

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:
>> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
>> + @.path + @.fn + ''''
>> + ' WITH
>> (
>> FIELDTERMINATOR = ''|''
>> ,ROWTERMINATOR = ''\n''
>> ,TABLOCK
>> )
>> '
>> EXEC( @.cmd ) ; -- BOMBS
>> -- EXEC sp_executesql @.cmd ; -- BOMBS
Naturally I've tried setting XACT_ABORT OFF (among other various
superstitious rituals). Nothing works.
TIA,
--joe
PS,
I don't have control of this server & the current admin & users aren't very
technical... As such I want to avoid using xp_cmdshell... similarly, I don't
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 to
have something that works stand alone that can run w/out intervention and a
minimum of admin.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
CR/LF).
Is there perhaps a way to set up DTS so it handles dynamic file names when
importing from ASCII? Any suggestions?
Thanx,
-joe
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
to go back to the @.#$ mainframe, hell, they were better off with 3x5
cards!!!!!!
Previous n.g. thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=28fd4c29145420ba&seekm=OQmAG3VcBHA.1848%40tkmsftngp05#link12
thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3BF9B2DC.65712B51%40drew.edu&rnum=1&prev=/groups%3Fq%3Dhandling%2Berrors%2BCould%2Bnot%2Bbulk%2Binsert.%2BFile%2Bgroup:microsoft.public.sqlserver.programming%2Bgroup:microsoft.public.sqlserver.programming%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dmicrosoft.public.sqlserver.programming%26selm%3D3BF9B2DC.65712B51%2540drew.edu%26rnum%3D1
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> 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:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> 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
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||I just came up with a ghastly hack for a solution... a tad dangerous
perhaps...
In the SP, I log the file in a log table before I run the BULK INSERT. The
SP checks in the log table see if an attempt has been made on that file name
before, and will loop if an attempt has already been made.
I then set up a 2-step job on the server. Both steps call the same SP, and
step 1 calls step 2 on failure, and step 2 calls step 1 on failure. Until
the SP runs out of possible file names (filename is based on date in a
xxxxmmddyy.txt format), it'll keep BULK INSERTing files it finds and failing
on ones it doesn't (the SP succeeds when it can exit without error, when it
runs out of names, the maximum of which is based upon today's date).
Finally, both job steps (whichever one happens to be running at the time)
exit the job on success (which is again, running out of file names to look
for).
As I said, a ghastly hack.
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> 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:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> 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
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||Before you start using Bulk Insert, can you confirm if the file really exists , you might want to use the command "xp_fileexist"|||"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> Before you start using Bulk Insert, can you confirm if the file really
exists , you might want to use the command "xp_fileexist"
No such xp_ on their machine, although xp_cmdshell is there, and worked at
one point in time. Plus, the admin situation is problematic such that xp_*
priveleges are revoked when service packs get applied.
This has to work w/out any special priveleges or assignments, just a job
that works reliably (nightly) w/out user intervention.
Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so even
if I knew the file existed, it doesn't protect the batch process from
interruption.
I *did* find a workaround to this problem. See my last reply to myself in
the thread. It's too bad that MS never got around to fixing these issues
(poor T-SQL error handling) until Longhorn.
Thanks,|||just a fyi, enhanced error handling has nothing to do with longhorn. it's to
do with yukon.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > Before you start using Bulk Insert, can you confirm if the file really
> exists , you might want to use the command "xp_fileexist"
> No such xp_ on their machine, although xp_cmdshell is there, and worked at
> one point in time. Plus, the admin situation is problematic such that xp_*
> priveleges are revoked when service packs get applied.
> This has to work w/out any special priveleges or assignments, just a job
> that works reliably (nightly) w/out user intervention.
> Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
even
> if I knew the file existed, it doesn't protect the batch process from
> interruption.
> I *did* find a workaround to this problem. See my last reply to myself in
> the thread. It's too bad that MS never got around to fixing these issues
> (poor T-SQL error handling) until Longhorn.
> Thanks,
>
>|||ahhhh, you are my oj in the mourning... and yukon correct me any time you
want when i get the code names for MS products mixed up
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"oj" <nospam_ojngo@.home.com> wrote in message
news:e3vmQWbqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> just a fyi, enhanced error handling has nothing to do with longhorn. it's
to
> do with yukon.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
> news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> > "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> > news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > > Before you start using Bulk Insert, can you confirm if the file really
> > exists , you might want to use the command "xp_fileexist"
> >
> > No such xp_ on their machine, although xp_cmdshell is there, and worked
at
> > one point in time. Plus, the admin situation is problematic such that
xp_*
> > priveleges are revoked when service packs get applied.
> >
> > This has to work w/out any special priveleges or assignments, just a job
> > that works reliably (nightly) w/out user intervention.
> >
> > Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> > BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
> even
> > if I knew the file existed, it doesn't protect the batch process from
> > interruption.
> >
> > I *did* find a workaround to this problem. See my last reply to myself
in
> > the thread. It's too bad that MS never got around to fixing these issues
> > (poor T-SQL error handling) until Longhorn.
> >
> > Thanks,
> >
> >
> >
>|||The SP "xp_fileexist" exists in Master db, not in the User DB.|||Thanks, I gather that it's new in SS2K?
It's not listed in the SS2K BOL in the T-SQL ref. Doesn't require additional
rights, great!
It still doesn't solve the problem with any other kind of fatal error in
BULK INSERT (a 0-byte file, for instance).
The bottom line is that MS SQL 7's & 8's error handling isn't very capable
and I'll still have to either go w/ a hacked workaround in the job manager
or a workaround in client software (Perl, WSE...).
What really makes me wonder is, why in the first place was BULK INSERT
implemented to throw fatal errors *at all?* It's an import utility, that's
all it is - it's not a table reference that is the subject of
latent/deferred binding. Makes me wonder about the underpinning API, the
(familiar) scent of old Sybase code lurking under the hood.
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:8AD1A570-60F4-48CF-B368-73369419CC27@.microsoft.com...
> The SP "xp_fileexist" exists in Master db, not in the User DB.|||Just to let you know you're not alone,
I posted this a few hours before you.
http://communities.microsoft.com/newsgroups/previewFrame.asp?ICP=msdn&sLCID=
us&sgroupURL=microsoft.public.sqlserver.programming&sMessageID=%253CuaBAWQSq
DHA.1884@.TK2MSFTNGP09.phx.gbl%253E.
At the moment I'm using this technic: in the sp that needs to bulk insert I
assign the text of the format file to a variable and create the file on the
fly (using a wrapper to the file system object), something like this:
/*********/
declare @.txt varchar(8000)
declare @.FormatFilePath varchar(255)
declare @.path varchar(255)
set @.txt='8.0
1
1 SQLCHAR 0 0 "\r\n" 2 Code ""
'
select @.path='Target.dat', @.FormatFilePath='c\winnt\temp\Target.fmt'
exec @.Res=dbo.st_WriteToFile @.FormatFilePath, @.txt --This is a FSO wrapper
that creates or overwrites a file with the specified text
if @.Res<>0
goto ErrorHandler
create table Target(
PKCheck int Identity(1,1),
Code char(15)
)
exec(N'bulk insert Target from '''
+ @.path
+ N''' with (FORMATFILE='''
+ @.TempDirPath
+ N''', TABLOCK)')
/**********/
It works fine, but of course if the data file or the table change their
structures you have to modify the sp, and you can't use third parties format
file without checking for their correctness.
I' was trying to generalize this approch by adding error controls when I
stumbled into your same problem.
Now I'm thinking of using BCP's format option via xp_cmdshell to produce a
valid format file, but of course I have to take into account the cases when
the number of table fields and file fields and their mutual positions
differ, but the problem of using format files not created by you remains.
Still working on.
Salvor
--
++++++++++++++
To e-mail me,
remove ".NO_SPAM" from my e-mail address