Intercepting Bulk Insert errors

Intercepting Bulk Insert errors

Post by Salvo » Thu, 13 Nov 2003 15:51:32



Hi,
I'm trying to handle Bulk Insert errors, but with no success so far.
I created a table like the following
create table Target(
   PKCheck int Identity(1,1),
   Code char(15)
)

I'm going to populate the table with data stored in a flat file which only
contains data for the field 'Code', using a format file like this one:

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

When I run:
Bulk Insert Target from 'Target.dat' with (FORMATFILE='Target.fmt')
everything works fine.

But if I use a bad format file like this one:
8.0
1
1 SQLCHAR 0 0 "" 0 PKCheck ""
2 SQLCHAR 0 0 "\r\n" 1 Code ""

I get the following error
Incorrect syntax near the keyword 'from'

Also with:
8.0
2
1 SQLCHAR 0 0 "" 0 PKCheck ""
2 SQLCHAR 0 0 "\r\n" 3 Code ""
I get:
Could not bulk insert. Error reading destination etc...

Great. That's what supposed to happen.
The problem is that these kind of errors cannot be intercepted even by using
exec or sp_executesql, or by enclosing the statement into its own stored
proc. Not only they cause the termination of the containing batch or stored
proc, but also that of the calling batch.
What I'd like to do is, for instance:
exec(N'Bulk Insert Target from ''Target.dat'' with
(FORMATFILE=''Target.fmt'')')

    print 'Bulk Insert error'

Instead the execution never reaches the if statement.
Any ideas?
TIA
Salvor

 
 
 

Intercepting Bulk Insert errors

Post by DHeatle » Thu, 13 Nov 2003 17:36:26


I think you could create a SP and use the Begin Trans...Commit Trans and do some error coding that way.
Such as:
Create PROC 'procname'
As
Begin Trans
'your code here'

Print 'error Message'
Rollback Tran
Else
Commit Tran

You could also set a placeholder for the error message, such as


Then in the code,



To bad Yukon isn't out yet, you could just use the TRY....CATCH error control!

     Hi,
     I'm trying to handle Bulk Insert errors, but with no success so far.
     I created a table like the following
     create table Target(
        PKCheck int Identity(1,1),
        Code char(15)
     )

     I'm going to populate the table with data stored in a flat file which only
     contains data for the field 'Code', using a format file like this one:

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

     When I run:
     Bulk Insert Target from 'Target.dat' with (FORMATFILE='Target.fmt')
     everything works fine.

     But if I use a bad format file like this one:
     8.0
     1
     1 SQLCHAR 0 0 "" 0 PKCheck ""
     2 SQLCHAR 0 0 "\r\n" 1 Code ""

     I get the following error
     Incorrect syntax near the keyword 'from'

     Also with:
     8.0
     2
     1 SQLCHAR 0 0 "" 0 PKCheck ""
     2 SQLCHAR 0 0 "\r\n" 3 Code ""
     I get:
     Could not bulk insert. Error reading destination etc...

     Great. That's what supposed to happen.
     The problem is that these kind of errors cannot be intercepted even by using
     exec or sp_executesql, or by enclosing the statement into its own stored
     proc. Not only they cause the termination of the containing batch or stored
     proc, but also that of the calling batch.
     What I'd like to do is, for instance:
     exec(N'Bulk Insert Target from ''Target.dat'' with
     (FORMATFILE=''Target.fmt'')')

         print 'Bulk Insert error'

     Instead the execution never reaches the if statement.
     Any ideas?
     TIA
     Salvor

 
 
 

Intercepting Bulk Insert errors

Post by Salvo » Thu, 13 Nov 2003 18:35:12


It's the same. It doesn't work because execution would stop immediately at
the offending bulk insert, without completing the sp.
Executing the statement with exec or in a stored proc is the same, in both
cases it runs in its own batch, and using an explicit transaction doesn't
change things.

Anyway, that's just what I was expecting it to do. If you execute this
batch:


exec('select * from NonExistingTable')


you get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'NonExistingTable'.
208

Doing the same with bulk insert doesn't get past the exec:

exec('Bulk Insert Target from ''Target.dat'' with
(FORMATFILE=''Target.fmt'')')


You get:
Server: Msg 208, Level 16, State 82, Line 1
Invalid object name 'NonExistingTable'.

Only the state is different. Maybe it has something to do with that.

I'm sure I'll be panning gold out of Yukon ;-)



Quote:> I think you could create a SP and use the Begin Trans...Commit Trans and

do some error coding that way.
> Such as:
> Create PROC 'procname'
> As
> Begin Trans
> 'your code here'

> Print 'error Message'
> Rollback Tran
> Else
> Commit Tran

> You could also set a placeholder for the error message, such as


> Then in the code,



> To bad Yukon isn't out yet, you could just use the TRY....CATCH error
control!

>      Hi,
>      I'm trying to handle Bulk Insert errors, but with no success so far.
>      I created a table like the following
>      create table Target(
>         PKCheck int Identity(1,1),
>         Code char(15)
>      )

>      I'm going to populate the table with data stored in a flat file which
only
>      contains data for the field 'Code', using a format file like this
one:

>      8.0
>      2
>      1 SQLCHAR 0 0 "" 0 PKCheck ""
>      2 SQLCHAR 0 0 "\r\n" 1 Code ""

>      When I run:
>      Bulk Insert Target from 'Target.dat' with (FORMATFILE='Target.fmt')
>      everything works fine.

>      But if I use a bad format file like this one:
>      8.0
>      1
>      1 SQLCHAR 0 0 "" 0 PKCheck ""
>      2 SQLCHAR 0 0 "\r\n" 1 Code ""

>      I get the following error
>      Incorrect syntax near the keyword 'from'

>      Also with:
>      8.0
>      2
>      1 SQLCHAR 0 0 "" 0 PKCheck ""
>      2 SQLCHAR 0 0 "\r\n" 3 Code ""
>      I get:
>      Could not bulk insert. Error reading destination etc...

>      Great. That's what supposed to happen.
>      The problem is that these kind of errors cannot be intercepted even
by using
>      exec or sp_executesql, or by enclosing the statement into its own
stored
>      proc. Not only they cause the termination of the containing batch or
stored
>      proc, but also that of the calling batch.
>      What I'd like to do is, for instance:
>      exec(N'Bulk Insert Target from ''Target.dat'' with
>      (FORMATFILE=''Target.fmt'')')

>          print 'Bulk Insert error'

>      Instead the execution never reaches the if statement.
>      Any ideas?
>      TIA
>      Salvor

 
 
 

Intercepting Bulk Insert errors

Post by joe » Sat, 15 Nov 2003 16:01:24


My final trick, for security, reliability & portability,
was to let the stored procedure crash, using the job
manager to keep the procedure running over and over again
until it exhausts the list of files. When step 1 of the job
fails, it runs step 2, and vice versa.

Quote:>-----Original Message-----
>Hi,
>I'm trying to handle Bulk Insert errors, but with no
success so far.
>I created a table like the following
>create table Target(
>   PKCheck int Identity(1,1),
>   Code char(15)
>)

>I'm going to populate the table with data stored in a flat
file which only
>contains data for the field 'Code', using a format file
like this one:

>8.0
>2
>1 SQLCHAR 0 0 "" 0 PKCheck ""
>2 SQLCHAR 0 0 "\r\n" 1 Code ""

>When I run:
>Bulk Insert Target from 'Target.dat' with

(FORMATFILE='Target.fmt')

- Show quoted text -

Quote:>everything works fine.

>But if I use a bad format file like this one:
>8.0
>1
>1 SQLCHAR 0 0 "" 0 PKCheck ""
>2 SQLCHAR 0 0 "\r\n" 1 Code ""

>I get the following error
>Incorrect syntax near the keyword 'from'

>Also with:
>8.0
>2
>1 SQLCHAR 0 0 "" 0 PKCheck ""
>2 SQLCHAR 0 0 "\r\n" 3 Code ""
>I get:
>Could not bulk insert. Error reading destination etc...

>Great. That's what supposed to happen.
>The problem is that these kind of errors cannot be

intercepted even by using
Quote:>exec or sp_executesql, or by enclosing the statement into
its own stored
>proc. Not only they cause the termination of the

containing batch or stored

- Show quoted text -

>proc, but also that of the calling batch.
>What I'd like to do is, for instance:
>exec(N'Bulk Insert Target from ''Target.dat'' with
>(FORMATFILE=''Target.fmt'')')

>    print 'Bulk Insert error'

>Instead the execution never reaches the if statement.
>Any ideas?
>TIA
>Salvor

>.

 
 
 

1. Error when bulk insert follows another large bulk insert

I am running a job out of MS SQL Enterprise Manager where the first step
turns off transaction logging, the 2nd step is a large bulk insert step,
and the 3rd step is another large bulk insert step.  I receive the
following error when executing the 3rd step.  Is this error occurring
because the database is still comitting data from the previous large bulk
insert at the same time as it is performing the next bulk insert?  If so,
is there a workaround?  Thank you for any help you can provide.

Error message from Step 3 (2nd bulk insert):
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized.
Reissue the statement after the current backup, CHECKALLOC, or
file manipulation operation is completed. [SQLSTATE 42000] (Error 3023)  
The statement has been terminated. [SQLSTATE 01000] (Error 3621).  
The step failed.

--
Posted via CNET Help.com
http://www.help.com/

2. Syntax error in Stor Proc to register new user to a website

3. BULK INSERT under bulk copy?

4. cluster errors

5. Bulk Delete (as opposed to Bulk Insert)

6. Databount greed control wanted

7. Bulk Insert / Bulk copy

8. Progress applikacation and Informix db

9. BULK INSERT or BULK COPY

10. Intercept implicit post on insert?

11. How to intercept INSERT and call Stored Proc ?

12. BULK INSERT and Error Trapping

13. Generic network error with a BULK INSERT statement