Why does a SQL Server Stored Procedure always time out when called from an Access application?
Posted on 2011-03-17
I am developing an Access 2007 "project" (.ADP) application as a front-end to data stored in a SQL Server 2005 Express database.
I have a Stored Procedure that I use to insert records into a Table, from a View that reads various pices of data from other Tables. The Stored Procedure, Tables and View all exist in my SQL Server database but the Stored Procedure is executed by some VBA coding in that Access application.
After 30 seconds exactly, I always get an error in the Access application when the Stored Procedure is called. Error Numb : -2147217871, Error Desc : Timeout expired
I have looked for various timeout settings but have found none that have any effect on this error. After 30 seconds the error appears.
I have tried changing the "Connect Timeout" and "General Timeout" values in the Access Server Connection properties, and as far as I can see, at the SQL Server end of things all the timeout settings are at 0, except for the "Failed Logon" timeout which is at 20 seconds.
Can anyone tell me which Access or SQL Server setting controls the timing-out of Stored Procedures?