Solved

Timeout Error

Posted on 2002-03-20
5
234 Views
Last Modified: 2011-09-20
Hello All...

can you please help me in the following  problem:

I have MS ACCESS DB that have a linked table to SQL Server, when I try to insert records from the ACCESS DB table into the SQL Server linked table,I get the following error:
ODBC-insert on a linked table 'Table Name' failed
[Microsoft][ODBC SQL Server Driver] timeout expires(#0).

FYI :
I'm using SQL SERVER 2000 ,MS ACCESS 2000, the linked table(sql server side) has more than 30,000,000 records.
0
Comment
Question by:Naser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Accepted Solution

by:
WebWanderer earned 50 total points
ID: 6884946
Have you considered changing the OLE timeout options? I have access 97 - it's under Tools, Options, Advanced - the OLE/DDE Timeout setting.

That should help, but I think that you will just be delaying your problem. You need to determine why the timeout has occured. Check to ensure that you are not having locking issues (all though it seems unlikely, since with a 30M plus table, it's unlikely to be table locked ...

Look at the number of rows that you are looking to insert... see if you can reduce the size of rows to be inserted in a single connection.

HTH
0
 

Author Comment

by:Naser
ID: 6890592
webwnderer ,,,thanx for ur help, but I have increase the OLE/DDE Timeout setting to 150 ,also I have reduce number of records to be inserted(1000 rec. at a time) , but still having the same problem,,
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 50 total points
ID: 6961470
Hi Naser,

Two things:
1. From your comment above you have declined or rejected the answer from WebWanderer. Can you formally reject the answer as many experts don't visit locked questions.

2. What is the severs performance like? Have you run a dbcc on the clustered index of all tables, along with an update statistics? This can be done via a maintenance plan or I can give you the SQL to do this ... Do you have indexes with correct fill-factors on these tables?

Q) Did this just sart doing this, ie the databse grew to x number of records and performance dropped off, or are you creating an access application which links to an existing system?

WebWanderer: Answers are more commonly proposed as comments ...

Regards
  David
0
 
LVL 3

Assisted Solution

by:avbsql
avbsql earned 50 total points
ID: 7025112
Are the table structure same as in Access and SQL. try to relink the tables after you have changed the option of timeout.
0
 

Expert Comment

by:CleanupPing
ID: 9280732
Naser:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question