Again, another post with something that seems obvious at the moment and something I've done many times and it works like a charm everytime. Last week I ran into a situation where I was performing a SqlBulkCopy on some data up to a Sql Server on a shared host. This process has run nightly for over a year, however Friday the structure changed slightly so my client called me in to make the changes. Changes are made and the process runs fine.
Moving forward about 6 hours....suddenly an exception is generated in the SqlBulkCopy (don't recall the exact wording on the exception) which seems to indicate that either my structures between source (Access) and destination are out of sync or perhaps there is some corrupt data. Since it worked fine previously in the day, I opted towards looking at the latter.
Quick once-over on the source data didn't show any problems and after doing some more preliminary searching....data appeared to be fine, but there were tons of records and I'm sure I missed something. So what was my secret tool in solving this problem...
Select Top 10 from myData
Just simply ran the the above select when selecting my source data, changing the Top XX to different values to zero in on the record that was causing the error. Once I narrowed it down to that one record, it was a simple matter to scan the data looking for a problem. Within seconds, I found the problem (calculated field which had a division by zero error), fixed the field and all was well.