OH good – thats what i wanted to hear.
Great advice.
I’m not sure why the stored proc fails exactly. It is a small proc that takes 3 params and I have very few records in the db at this point. I have decent indexing on the SARGS so I am fairly confident in the SP. I was thinking maybe it was a time out issue trying to connect to the DB. I was also nervous that there may have been an issue releasing resources in the functoid code, but since i didnt code it, and i believe someone from microsoft did, I want to think there should not be a problem there either.
I will look into some of your suggestions further. Our testing / production environments should have the proper resources, at least the min requirements. I also found out that we can tweak our other file grabber program, it loops through the files and copies one at a time & we can insert a variable seconds wait between files if we need to. I’m interested to look into the file adapter batch size you mentioned as well.
THANKS for your advice!!!