Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data th
en
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--