TheRealJabberwocky
Programmer
I am using a DTS package on Server B that has code in it to select certain rows from Server A, copy the records to Server B and finally remove the original record from Server A. Any form of replication is out of the question. The DTS package has worked well until we ran into a real stumper using millisecond time as a way to order the cursor records.
Previous processing used the DTS package to copy all of the records and THEN delete them (was capable of moving 20K records per minute - acceptable). It turns out we need to delete them as we move through the cursor (is capable of moving 2 records every 5 seconds - not acceptable). The problem is using an updateable cursor on a linked server - BUGS GALORE in SQL Server 7.0 Microsoft reports. Nice.
Any ideas to speed this up? Why did it slow down so much simply by moving the delete statement inside the cursor? So many questions, so little time...
Previous processing used the DTS package to copy all of the records and THEN delete them (was capable of moving 20K records per minute - acceptable). It turns out we need to delete them as we move through the cursor (is capable of moving 2 records every 5 seconds - not acceptable). The problem is using an updateable cursor on a linked server - BUGS GALORE in SQL Server 7.0 Microsoft reports. Nice.
Any ideas to speed this up? Why did it slow down so much simply by moving the delete statement inside the cursor? So many questions, so little time...