colinasad
asked on
Why does a SQL Server Stored Procedure always time out when called from an Access application?
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?
Many thanks.
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?
Many thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the prompt responses.
To be honest, I'm not sure if I'm using "ODBC" or "ADO".
I have attached my VBA procedure that I use for executing Stored Procedures in my SQL Server database from my Access application. I pass in the name of the Stored Procedure (SP) and a collection of parameters to be sent to the SP.
I declare a "ADODB.Command" object, which eventually gets executed.
I have just had a look at that code and notice that there is a "cmdCommand.CommandTimeout " property that exists but which I am not currently setting.
lludden : Is that what you are refering to?
I'll experiment with that and get back to you.
Many thanks. Colin.
To be honest, I'm not sure if I'm using "ODBC" or "ADO".
I have attached my VBA procedure that I use for executing Stored Procedures in my SQL Server database from my Access application. I pass in the name of the Stored Procedure (SP) and a collection of parameters to be sent to the SP.
I declare a "ADODB.Command" object, which eventually gets executed.
I have just had a look at that code and notice that there is a "cmdCommand.CommandTimeout
lludden : Is that what you are refering to?
I'll experiment with that and get back to you.
Many thanks. Colin.
Public Sub Execute_SP_Params_NoRS(strSPName As String, Params() As myParam)
' Execute SQL-Server Stored Procedure residing in the SQL Server Database
' Stored Procedure has multiple parameters, but does not return a recordset
' Open and Close DB Connection can be done here because no RS is being returned
' NB There is also "Execute_SP_Params_RS" that returns a recordset
' NB There is also "Execute_SP_Simple" for single/none parameter SPs
On Error GoTo HandleError
' Simple integer to count through the parameters
Dim i As Integer
' Declare a new Command Object
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
' Declare a possible parameter
Dim parParam As ADODB.Parameter
' Put the EggTimer cursor on
DoCmd.Hourglass True
' Open the connection to the database
Call OpenDBConnection
' Set up the Command Object to execute as Stored Procedure
cmdCommand.CommandType = adCmdStoredProc
' Set the Command to the current connection
Set cmdCommand.ActiveConnection = cnConnection
' Set the SQL Statement to the command text
cmdCommand.CommandText = strSPName
' Now add the parameters to the command
For i = 0 To (UBound(Params) - 1)
Select Case Params(i).paramType
Case adDate
Set parParam = cmdCommand.CreateParameter(Params(i).paramName, adDate, Params(i).paramKind, , Params(i).paramValue)
Case adCurrency
Set parParam = cmdCommand.CreateParameter(Params(i).paramName, adCurrency, Params(i).paramKind, , Params(i).paramValue)
Case adDouble
Set parParam = cmdCommand.CreateParameter(Params(i).paramName, adDouble, Params(i).paramKind, , Params(i).paramValue)
Case adInteger
Set parParam = cmdCommand.CreateParameter(Params(i).paramName, adInteger, Params(i).paramKind, , Params(i).paramValue)
Case adVarChar
Set parParam = cmdCommand.CreateParameter(Params(i).paramName, adVarChar, Params(i).paramKind, Max_SQLStr_Len, Params(i).paramValue)
Case adBoolean
Set parParam = cmdCommand.CreateParameter(Params(i).paramName, adBoolean, Params(i).paramKind, , Params(i).paramValue)
End Select
cmdCommand.Parameters.Append parParam
Next i
' Execute the Command against the database
cmdCommand.Execute
' were there any OUTPUT parameters to be passed back via params
For i = 0 To (UBound(Params) - 1)
With Params(i)
If .paramKind = adParamOutput Then
.paramValue = cmdCommand(.paramName)
End If
End With
Next i
' Close the connection to the database
Call CloseDBConnection
' Put the EggTimer cursor Off
DoCmd.Hourglass False
Exit Sub
HandleError:
genErrorHandler Err.Number, Err.DESCRIPTION, "DB_LOGIC", "Execute_SP_Params_NoRS - " & strSPName
' Put the EggTimer cursor Off
DoCmd.Hourglass False
Exit Sub
End Sub ' Execute_SP_Params_NoRS
ASKER
That's the property that is controlling my timeout. I didn't know it existed.
Many thanks.
Many thanks.
-R