BULK INSERT crashes Stored Procedure ?

BULK INSERT crashes Stored Procedure ?

Post by buddhashortfatgu » Thu, 13 Nov 2003 22:53:10



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.

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.

 
 
 

BULK INSERT crashes Stored Procedure ?

Post by buddhashortfatgu » Thu, 13 Nov 2003 23:43:00


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

cards!!!!!!

Previous n.g. thread:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=rig...
thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3...

--

+-------------------------
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.

> 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.


 
 
 

BULK INSERT crashes Stored Procedure ?

Post by buddhashortfatgu » Fri, 14 Nov 2003 01:05:37


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

> 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.

> 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.

 
 
 

BULK INSERT crashes Stored Procedure ?

Post by buddhashortfatgu » Fri, 14 Nov 2003 08:17:11



Quote:> 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,

 
 
 

BULK INSERT crashes Stored Procedure ?

Post by oj » Fri, 14 Nov 2003 09:16:06


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




> > 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,

 
 
 

BULK INSERT crashes Stored Procedure ?

Post by buddhashortfatgu » Fri, 14 Nov 2003 15:53:14


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

> 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





> > > 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,

 
 
 

BULK INSERT crashes Stored Procedure ?

Post by somegu » Sat, 15 Nov 2003 00:08:46


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.


Quote:> The SP "xp_fileexist"  exists in Master db, not in the User DB.

 
 
 

BULK INSERT crashes Stored Procedure ?

Post by Salvo » Sat, 15 Nov 2003 10:22:21


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...
us&sgroupURL=microsoft.public.sqlserver.programming&sMessageID=%253CuaBAWQSq

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:
/*********/




'8.0
1
1 SQLCHAR 0 0 "\r\n" 2 Code ""
'


that creates or overwrites a file with the specified text

   goto ErrorHandler

create table Target(
   PKCheck int Identity(1,1),
   Code char(15)
)

exec(N'bulk insert Target from '''

     + N''' with (FORMATFILE='''

     + 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

 
 
 

1. Bulk Insert and Stored Procedure

Hello,

I'm currently running a VB exe that will create a binary flat file and then
call a stored procedure to Bulk Insert into a table.  Everthing seems to be
work great.  But I would like to pass it the path where the Flat file is
located.  I can't seem to get it to work.
Does any one have some suggestions.


can replace the actual text string. but I can not.


BULK INSERT cash_jerry.dbo.[history table] FROM
'D:\eCash_History\ecash_flatfile.txt'
     WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = ';',
   ROWTERMINATOR = '|')
GO

Thanks in Advance

2. Primary Key

3. BULK INSERT in a Store procedure

4. How can I use parameters using Oracle Sql Query in Data Environment Designer of VB-6?

5. bulk insert stored procedure

6. Error 8623 on Select over linked server.

7. Bulk Insert and Stored Procedure

8. Informix DBAs needed for Cincinnati, Ohio-U.S.-(Recruiter)

9. Syntax:Bulk Insert from Stored Procedure

10. Error when bulk insert follows another large bulk insert

11. calling stored procedure on insert trigger with inserted values as parameters

12. Bulk Delete (as opposed to Bulk Insert)

13. BULK INSERT or BULK COPY