 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / June 2006transferspreadsheet is very slow
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=%2finternational.aspx. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. 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. 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 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 [quoted text clipped - 17 lines] > > Tore |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.