Intercepting Bulk Insert errors

  • 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'')')

    if @@Error<>

    print 'Bulk Insert error'

    Instead the execution never reaches the if statement.

    Any ideas?

    TIA

    Salvor

    Salvor


    Salvor

  • I do not know what your "flat file" looks like, but if it contains values for your PKCheck identity column, try

    SET IDENTITY_INSERT TableName ON

    If PKCheck data is NOT in your flat file, Create a VIEW as

    SELECT Code FROM TARGET

    then Bulk Insert into the view, then drop the view.



    Once you understand the BITs, all the pieces come together

  • As you can see from my example format file, the field PKCheck is not supposed to be loaded from the flat file. In this case tha flat file would only contain one column of data that will be placed in the Code field. I made up this example just not to use something too trivial.

    My problem is not how to manage things for a successful loading (or living ;), but how to intercept a possible error. As the time being, every bulk insert error makes the sp that executed it and every sp up in the calling chain bomb out.

    Anyway thanks for replying.

    quote:


    I do not know what your "flat file" looks like, but if it contains values for your PKCheck identity column, try

    SET IDENTITY_INSERT TableName ON

    If PKCheck data is NOT in your flat file, Create a VIEW as

    SELECT Code FROM TARGET

    then Bulk Insert into the view, then drop the view.


    Salvor


    Salvor

  • Error handling has always been IMHO the weakest point in SQL Server. I would use BulkInsert DTS Taks to handle the errors in an easier way!

    You could increase the MAX_ERROR number in the statement but that does not help much.


    * Noel

  • Unfortunately increasing MAXERRORS doesn't help at all, because when the format file is wrong the error occurs before loading.

    Also, I'm strictly bound to using sp's, no DTS.

    Well, I think we'll have to wait for Yukon to see some improvement on the subject.

    Salvor


    Salvor

  • Have you try just to insert to a table with 1 column of identity and 1 column of data.

    Once you got it into the database you could parse and look for space and set it as your 1st column of data and the rest to be set as your 2nd data.

    If this is not what you want chances are you could still get what you want because you can write sql to manipulate the data until you get what you want.

    mom

  • Maybe I haven't made myself clear enough.

    The problem is not about the data: using the first format file example in my initial post I can load the flat file without any error. Troubles arise whene the format file is wrong (like in the other two examples of mine), that is BEFORE the loading can start. Could be either the syntax, or referencing a non existing target column, or even an empty format file.

    Salvor


    Salvor

  • Check out

    http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17942



    Once you understand the BITs, all the pieces come together

  • ThomasH,

    I had already thought of using DMO or a JOB, but I decided to use them as a last resort, mainly becouse I think they would be an inelegant solution to my problem (don't get me wrong, I would use your or GRN's suggestions in other situations, I simply don't like the idea of using an external 'tool' to execute a SQL statement from inside SQL Server).

    Actually, my question has now turned more into a philosophical one, since at the moment I'm dynamically creating my format files in the same sp executing the bulk insert, so once it works, it'll work forever unless someone change the structure of the target table, and that shouldn't happen without control.

    Still I keep wondering why SQL Server programmers at MS decided to make it work like this. It should be just like any other SQL statement. I mean, when you execute this batch:

    declare @Res int

    exec('select * from NonExistingTable')

    select @Res=@@Error

    if @Res<>0 print @Res

    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:

    declare @Res int

    exec('Bulk Insert Target from ''Target.dat'' with

    (FORMATFILE=''Target.fmt'')')

    select @Res=@@Error

    if @Res<>0 print @Res

    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.

    Maybe they just forgot it (like other things).

    Salvor


    Salvor

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply