ODBC call failed [Microsoft][ODBC SQL Server Driver]Timeout expired (#

[ssatech]

Getting this error:
ODBC call failed [Microsoft][ODBC SQL Server Driver]Timeout expired (#0)on other network computers

SQL Server 2000 BE
Access 2003 FE

I have command button that runs 56 update queries which opens a Trial Balance Report.
I have tried changing the ODBC Timeout setting on each of the queries to: 0, 60, 3000 but to no avail.
When this report is run on one of the computer in the network. The "ODBC call failed [Microsoft][ODBC SQL Server Driver]Timeout expired (#0)starts to happen on my computer and subsequent other computers.


Now this seems to happen in all the computers running the application even without running this report.

Tried stopping and restarting SQL Server and even rebooted our main server. Problem free only for a short-time, but again ""ODBC call failed [Microsoft][ODBC SQL Server Driver]Timeout expired (#0)"

Any ideas what could be causing this?

 

[mrdenny]

Sounds like some blocking is happening on the SQL Server. Fire up SQL Profiler and have it show you the errors. That will give you some better information.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ssatech]

Thanks Denny,
I'll try it out and let you know...

 

[fuzzyocelot]

ssatech, if you figure out what the solution is could you please post it?

We're having the same problem when a user runs a certain stored procedure from within an Access project which is connected to a SQL 2000 database. The stored procedure updates and inserts data into several tables. I can double-check, but I'm pretty they are using Access 2002.

I'm a DBA, and I ran a trace using SQL Profiler. Nothing unusual showed up in the trace, and I set it to show everything under stored procedures and everything else I could think of. I also watched the Activity Monitor and could see nothing blocking it. I even ran the stored procedure manually from within Management Studio and it ran fine.

The developer refreshed the link to the stored procedure as well. No major changes were made to the Access project or to the code. It used to work fine and we don't know what changed. We are stumped but still looking into it. We would also appreciate any suggestions.

Thanks,
Rebecca

 

[fuzzyocelot]

I found out some more information about the problem we're having. The developer is using a regular MS Access mdb and not a project. He's referencing the stored procedure via a pass-through query and executing it by using the DBEngine object:

DBEngine(0).(0).Execute "queryName", dbSeeChanges
DoEvents
DBEngine.Idle

I can re-create the problem from within Access but still don't have a solution. Since this could be an Access issue, I'm going to try posting in that forum.

Thanks,
Rebecca

 

[fuzzyocelot]

Eureka! I think I figured it out. At least it works for me. I don't know if it will work for you or not but I hope it does.

In Access on the Advanced tab of the Options menu under Tools, I set the OLE/DDE timeout to at least 60 seconds and the Refresh interval to 120 seconds. After that I didn't get the error message! The ODBC refresh interval was set to 1500 which didn't do squat for me. The ODBC timeout was set to 60 seconds within the query itself and that didn't do anything. So hopefully this will help.

Good luck!
Rebecca