transferspreadsheet is very slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The customer uses windows 2000 and office 2000. I have made an access 2000
project (.adp file) as client connected via network to sql server 2000
server.

The problem is that import of excel worksheet via the client has become so
slow. It takes 10 minutes to import 4000 lines / 14 columns from excel. Some
weeks ago the same operation was done in a few seconds.

In the Access client i make use of docmd.transferspreadsheet,
acspreadsheetTypeExcel9, <tablename>, <filename> etc.

The import table on sql server is declared with all columns as nvarchar(50),
null allowed, no primary key. SQL Server login or windows login does not
matter, both are equally slow. The import is finally done correctly, but time
consumption is not acceptable.

Any suggestions?

Regards

Tore
 
Hello Tore,

I suspect this issue on SQL Server side. YOu may want to test if the issue
occurs on a different client machine. If possible, you may want to use a
different SQL Server and create a new ADP to do the import job to see if
makes any difference.

If it is a server side issue, you may want to use SQL Profiler to trace the
workload to see if there is blocking issue. Also, please get sysprocesses
and syslockinfo to see details:

INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453

NF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=/international.aspx.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
you should use DTS; it is a lot faster than that.

your other options include

openrowset
opendatasource
openquery

and linkedservers I believe.

a hundred ways to get this done.


PS - are you running this on a WAN on a LAN?

-Aaron
 
The problem occurred on other client machines as well. For my case the
problem is solved. Since the import-job is to be performed by two
"administrators" only I moved the import from excel to DTS. Via DTS it is
possible to import this spreadsheet in 2 to 4 seconds into the SQL Server
table. Time consumption in connection with DoCmd.Transferspreadsheet is still
a mystery to me, but I found another solution (DTS).

Thanks for your comments.

Regards

Tore
 
Hello Tore,

Thank you for taking time to write in about the status of the issue. Since
other clients also have this issue, it shall be a blocking issue on server
side. We need to check profiler trace/blocking script result to find more
clues.

Anyway, great to hear you found a workaround by using DTS. This shall be
more convenient since you use SQL Server as backend.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top