Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why does a SQL Server Stored Procedure always time out when called from an Access application?

Posted on 2011-03-17
4
Medium Priority
?
906 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:colinasad
[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
  • 2
4 Comments
 
LVL 7

Expert Comment

by:RemRemRem
ID: 35158519
If you're running your SP through a pass through query using an ODBC connection, as I often do for ease of management, there's a property setting on the query itself that is "ODBC Timeout" which can either (dangerously) be changed to 0 or to a larger, more realistic timeout number.

-R
0
 
LVL 18

Accepted Solution

by:
lludden earned 2000 total points
ID: 35158728
If you are using ADO, and using ADO.Command, you need to set the ADO.CommandTimeout
0
 

Author Comment

by:colinasad
ID: 35159009
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.
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

Open in new window

0
 

Author Closing Comment

by:colinasad
ID: 35159063
That's the property that is controlling my timeout. I didn't know it existed.
Many thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

721 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