Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-04 : 09:13:48
|
| Michael Sumerano writes "This is a rather difficult question. At least to me anyway. We have an AS/400, which stores and processes ALL orders no matter where they come from. Our B2B site uses SQL Server 7.0 SP3 as its database and we send the orders from it to the AS/400 in the required format. The original design used cursors to send orders one-by-one through a custom VB component. It was VERY slow (especially when our orders shot through the roof recently), but it allowed us to verify EACH order to make sure it was valid and also to mark it as processed.Now, we use DTS to do a straight data transformation and send the orders in bulk and mark them as processed when it's finished. However, when it runs into a problem sending a specific record, the records that were sent succesfully don't get marked as processed and when it tries again it sends the same orders AGAIN! :)This is obviously not the solution we had in mind, but it is definitely the fastest. To process 100 records, it took about 3-5 minutes using the cursor method, and less than a second the DTS way.Is there any way we can transfer the records quickly without duplicating already transferred records when a failure occurs and the process runs again? I thought it had something to do with transactions, but they are turned on for the data transformation. My guess is that they don't work with DB2 on the AS/400. I could be wrong." |
|
|
sumo
Starting Member
45 Posts |
Posted - 2001-12-06 : 14:15:36
|
| To respond to my own question, the only way we have come up with to do this is to build a stand-alone VB program that gets run by the NT/2000 task scheduler. In this program, we would query the order database for the orders that need to be processed, then loop through them and one-by-one, insert them into the table on the other machine (it's an AS/400), and update them on our side as processed. This may not be that slow since we're not opening and closing a connection to the other machine each time we do an insert (that was the old way).I'm still looking for more ideas with this if anyone has them. Thanks!Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/Edited by - sumo on 12/06/2001 14:44:50 |
 |
|
|
|
|
|
|
|
Sending data to another non-sql server server and marking each row as processed