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

    Unanswered: Retrieve truncated data from BULK INSERT?

    Hi everyone. Does anyone know if you can retrieve truncated data from a BULK INSERT operation?

    We have a file that needs to be inserted into a SQL Server Database. There is a field that has a maximum of 8000 characters, but some times users submit files that have more than that. We need to be able to capture the truncated data. The BULK INSERT operation does not throw an error. The only way I can think of to get the data is if I bulk insert the data into a temporary table with a memo field and then copy it over, but that may really slow down the SP.

    Has anyone encountered this situation before? I also have the option of parsing the file in .NET.

    Thanks and take care,

    Angel

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    what is a memo field? do you mean text or ntext?

    also, if you are on 2005 you can use varchar(max) for the column type and you won't have this problem.

    EDIT: you can get force the bulk insert to terminate on the first error if you use the MAXERRORS option and set it to 1.
    http://msdn2.microsoft.com/en-us/library/ms188365.aspx
    Last edited by jezemine; 01-10-07 at 12:57.

  3. #3
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    The bulk insert does not throw a warning/error? Is the setting ANSI_WARNINGS set to OFF?

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Lexiflex
    The bulk insert does not throw a warning/error? Is the setting ANSI_WARNINGS set to OFF?
    the default value of MAXERRORS is 10, so if there are less than 10 truncations, it won't fail.

    However, if you use the ERRORFILE option, you can discover if there were any rows that failed to make it in even when MAXERRORS is not reached.

  5. #5
    Join Date
    Jan 2007
    Posts
    9
    Hi again everyone. Wow, thank you for all of the replies.

    First, yes, memo = text. Sorry, I learned the name first as memo somewhere else ...

    Second, should BULK INSERT throw an error during a truncation? I really do not want the insert to fail - I just want to capture the truncated data. If I changed it to do a BULK INSERT with the ERRORFILE option, will the ERRORFILE file be filled with the rows that were truncated?

    Also, we are using 2005 for some DBs, but not this one. The limitation on the field is a requirement. The goal is to capture the truncated data and report it back to the user.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would load it to a staging table and change the datatype to text. Then I would find al the rows that exceed 8000 using DATALENGTH(Col)>8000 in the predicate
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Yeah, the staging table is the best option. There's no way to capture the truncated data during load.

  8. #8
    Join Date
    Jan 2007
    Posts
    9
    Has anyone used the ERRORFILE option? Is it available in SQL Server (actually MSDE)? If it captures truncated rows and stores them in a file, where does it store them? Is that file something I can reach with .NET? Thank you everyone for helping me!

  9. #9
    Join Date
    Jan 2007
    Posts
    9
    FYI: The ERRORFILE option doesn't seem to be available in 2000. Thanks again everyone!

  10. #10
    Join Date
    Jan 2007
    Posts
    9
    Quote Originally Posted by Lexiflex
    The bulk insert does not throw a warning/error? Is the setting ANSI_WARNINGS set to OFF?

    Actually I don't know if ANSI_WARNINGS is set to off or on since it's not explicitly assigned. I would assume it's turned on because the warnings are thrown back to .NET. Also, when I run the stored proc in Sql server Management studio, I receive the warning error messages.

Posting Permissions

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