Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    9

    Lightbulb Unanswered: SS2005: TRY & CATCH with BULK INSERT

    SQL Server 2005

    Hi Everyone. We have a stored proc that performs several bulk inserts. I need to find a way to allow the bulk insert to truncate data. Also, I would like to be able to send back to .NET the exact line that failed if a failure does occur.

    Right now, the stored proc fails because of truncation. Could this be because there's a check for @@ERRROR <> 0 right after the bulk insert? Does anyone know if a truncation occurs, if that will "throw" an error to the catch block? This is NOT what we want. Can anyone help me to understand how to do this correctly?

    Thanks,

    Angel

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    check out the MAXERRORS and ERRORFILE options:

    http://msdn2.microsoft.com/en-us/library/ms188365.aspx

    BULK INSERT will fail if there are more truncations or any other type of error than the value specified in MAXERRORS. by default this value is 10. If you wanted to allow all truncations to be ignored, set MAXERRORS to a very high number.

    If you specify the ERRORFILE option, it will write information about the failed rows in a file you specify.

  3. #3
    Join Date
    Jan 2007
    Posts
    9

    Unhappy

    Jezemine,



    I played around with it today. There's only 1 record in the file, and SQL Server 2005 won't insert the record with truncation even with the default value of 10 for MAXERRORS.

    I wish there were a way to say truncation on VARCHAR fields is okay! For now, I'm just going to have to assume the field will be okay.

    I had already looked into MAXERRORS and the ERRORFILE but neither of them take care of things the way we want them done. Thank you for replying, though! I really do appreciate it!

    Take care,

    Angel

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ah, i get it. I didn't realize you actually wanted to allow the row in, but truncated. I don't think bulk insert would allow this. sql server (rightfully, imo) considers this data loss, and won't allow it.

    Can you increase the width of the column in your table?

  5. #5
    Join Date
    Jan 2007
    Posts
    9

    I wish ...

    Quote Originally Posted by jezemine
    ah, i get it. I didn't realize you actually wanted to allow the row in, but truncated. I don't think bulk insert would allow this. sql server (rightfully, imo) considers this data loss, and won't allow it.

    Can you increase the width of the column in your table?
    Nope. The client SHOULD make sure the data is less than 50 chars, but I would rather be able to just truncate. This particular piece of data isn't as important as the other data that gets processed after it - truncation would not be a big deal, but now it will stop the entire process.

    Thanks and take care

    Angel

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    in that case I think you have two options:

    1. parse the file beforehand to truncate the entries yourself, using perl or something similar
    2. or, you could bulk insert into a scratch or temp table that IS wide enough, and then insert into the real table from the scratch, calling SUBSTRING() as appropriate so you are guaranteed the value will fit.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •