Aaggh! Desperate for help with trapping errors in a bulk insert!

[JackTheDog]

any suggestions on how to capture 'converting varchar to numeric' error (level 16, state 5). We've tested our code and if the error is a 'constraint' error (level 15, state 1) no problem. But if we try to insert varchar into a numeric field (which is the problem we most often encounter in our data) no go. The ONLY difference is the type of error. Are some errors trapable and some not????

 

[foxdev]

I hate this error. Real hate. The problem (at least when it happens to me) is what is supposed to be a numeric column in the source that is blank. Here are my solutions (pick one; eggroll is included):

1) have whatever creates the source file put in zeros instead of blanks;
2) import into a character column (as a temporary column or in a temp table), then use CAST to insert it into the final table
3) you might try setting the "keep nulls" option on.
Robert Bradley

http://www.foxdev.com/

Do you have too much money? Visit

http://www.agrainofhope.org

 

[palbano]

Also depending on your operational parameters getting BizTalk into the process could solve your problem by converting the proprietary format into XML prior to inserting into the database. Although I have no idea as yet what performance issues might be found when dealing with massive amounts of data.

-pete