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.
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.
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!
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.