?
Solved

Timeout Error

Posted on 2002-03-20
5
Medium Priority
?
235 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 100 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 100 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 100 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 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