January 6, 2002 at 5:31 am
I am using SQL Server 2000 with Access 2000. I have a main Contact file with 700,000 records. When I run stored procedures on this file it only processes around 10,000 records at a time so I have to run each procedure several times. Has anyone come across this? I guess there must be a max record limit set somewhere but I can't find it.
Thanks!
Jane
January 6, 2002 at 6:00 am
I know of no limit to how many records you can address - other than when you just run out of disk space to hold them!
Operations involving 700k records are not usually a good idea, can you give us insight into what you are doing and how often? Also, lets look at other causes for your proc to not finish - one might be a timeout/rollback.
Andy
January 6, 2002 at 8:43 am
Thanks for your response Andy. The kind of procedures I am running on the full data set include:
- creating a full list of Contacts to send to an outside agency (but it only picks up the first 10,000)
- updating flags on the main table when various time-based events occur (eg no payments received for the last 6 months) - but again it only picks up the first 10,000.
It may well be timeout (I sometimes get that message too), is there any way I can increase the timeout?
Thanks again
Jane
January 6, 2002 at 10:01 am
What happens when you execute the stored procedure say from Query Analyzer. I am wondering if Access is the limitation, though I know in my organization we've import ~ 200K records from SQL Server to Access.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 6, 2002 at 11:02 pm
I doubt Access is the issue - thought it does have its own query specific timeouts you can adjust if you're running something long - but its better to drive it from SQL than Access (in other words export from SQL to Access rather than import from SQL into Access).
If you're using ADO you can set the timeout value for commands on either the connection or command object.
Could be you need better indexes. Maybe you also need to look at how you're exporting...doing it row by row? Set based? Could you give us some details on that part also?
Andy
January 7, 2002 at 10:08 am
You could turn this into a dts package. That would simplify the administration of the stored procedure. And to boot, I'm pretty sure you can export the results to an access db, if that is required.
Good luck
Tom Goltl
January 7, 2002 at 11:19 am
I'd choose DTS.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply