• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Timeout Error

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
Naser
Asked:
Naser
3 Solutions
 
WebWandererCommented:
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
 
NaserAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
avbsqlCommented:
Are the table structure same as in Access and SQL. try to relink the tables after you have changed the option of timeout.
0
 
CleanupPingCommented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now