I am developing a VB.NET app to handle the integration of a legacy PICK/Unidata accounting/ERP system to a warehouse management system (WMS) developed with a SQL2000 db back-end.

The integration app runs every 15 minutes, dumping data into a custom "integration db" that is located on the same SQL server instance as the production WMS db.

Here is how the VB.net app works:
Step 1: Download all changes from various files (tables) from legacy db via ODBC to vb.net dataset, then export to text files.
Step 2: Import text files via BULK INSERT into staging tables in the integration db, then copy the data using SPs into more permanent tables in the integration db.
Step 3: Using SPs, compare various tables between the integration db and the production db to determine what data needs to be transmitted to the WMS system. <--- this is where the failures have occured
Step 4: Take the records that have been flagged for download in step 3 and throw them out to the WMS system via XML files.

The volumes here are relatively light - a few thousand records at most.

So everything works great until it get to step 3. Occasionally, the SPs doing the cross db comparisons will take forever (5+ minutes) or occasionally the (non-cross-db) SPs in step 4 that help to compile the XML data will take 5+ minutes. Oddly there a times when this error happes very consistently - and if I pause the VB.net app before it gets to step 3 or 4 during these times I can run the same SPs from Query Analyzer, and they complete almost instantly.

I've tried READ UNCOMMITTED isolation levels, and NOLOCK tables hints and everything under the Sun I can dream up. These SPs are very straight forward, I've ripped all the begin/rollback/commit transaction statements out of them, yet still they hang. I've looked at the open transaction count of the processes before they run these SPs and they are zero, but during execution when they are hanging it shows that there are two open transactions. The longest wait type seem to be PAGEIOLATCH_SH to the production db ... but again I don't understand how that can cause this problem because running the same SP in query analyzer works so quickly ... ??!!

The only thing I can think of is that the BULK INSERT statements followed up by the cross-db statements using the same connection (vb.net connection pool) are really cheesing off SQL server. Thanks for any help with this.

Scott Fitz
defacto DBA