I build a local cursor or table and keep appending each batch until I have all the data. Remember, for me this is ad hoc, not inside an app. One thing is to save the data to a table instead of a cursor so you have a way to start over from part way through. Maybe save the name of the temp table so you can delete it when you're all done.
My queries don't return all that much data, but sometimes take several hours to run and cover several years worth of data.
I've also learned to run them overnight, or while I'm doing something else.
>Cindy,
>
>Thank you for the quick reply.
>
>Yes, in fact I do have a delimiting feature already built in, but that is for the user to set. However I am writing this as a generic class where I can not know what the data will look like before hand, so I can not make those kinds of guesses.
>
>I guess what it comes down to is, I need a way of returning a cursor on it's own thread???
>
>-- Chris
>
>
>>Chris,
>>
>>When I do large ad hoc SQL Pass-through queries I usually adjust the WHERE clause to get a few months of data at a time. You could also do a few letters of the alphabet if the data is alphabetical.
>>
>>
>>
>>>Hi everyone.
>>>
>>>I have come across a situation where I need to background fetch a large result set (1.2 million records or more) from SQL Server...
>>>
>>>Is there some combination of asynchronous, batch mode on/off and SQLMoreResults that will let me break apart the query and come back with manageable chunks at a time?
>>>
>>>Thanks for your help.
>>>-- Chris
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement