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,

Angelwhat 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|||The bulk insert does not throw a warning/error? Is the setting ANSI_WARNINGS set to OFF?|||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.|||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.|||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|||Yeah, the staging table is the best option. There's no way to capture the truncated data during load.|||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!|||FYI: The ERRORFILE option doesn't seem to be available in 2000. Thanks again everyone!|||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.