Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2003
    Posts
    8

    Angry Unanswered: Views are timing out

    We have an Access 2000 ADP, which we "compile" to an ADE, that fronts a SQL Server database. The users run a batch file that copies the ADE to their local hard drive and runs it from there. Lately, some of our more complex views have started timing out. I'm assuming that is due to the increased size of the database.

    I have optimized the tables and indexes as much as I can. I now need to bump the CommandTimeout for the project. However, I think I'm going blind, because I can't see how to do that. I can adjust the ConnectTimeout by clicking File/Connection/Advanced, but there is no way to adjust the CommandTimeout that way. There are tons of code snippets floating around that show you how to change CommandTimeout within a module, but not for the entire project. And, of course, CurrentProject.Connection.CommandTimeout is read-only; you can't adjust it after the application starts running. Catch-22, indeed!

    Has anyone run across this before and how did you overcome it? I'm quite sure that the answer will be very simple and very obvious and I'll be slapping my forehead and yelling, "D'OH!!", so I'll just say thanks in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you worked out using PROFILER and submit it to INDEX TUNING WIZARD for index recommendation.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2003
    Posts
    8

    Unhappy

    Oh, yes. I have optimized until it bleeds.

    I'm convinced that the problem is in the CommandTimeout as I indicated. I just can't figure out how to change it for the entire ADP!
    Aaaarrrgghhh!

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    How about SP levels on ACCESS, SQL Server & OS?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Apr 2003
    Posts
    8
    Current on everything.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If everything is on place, then last resort is to contact MS Support for a fix.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Apr 2003
    Posts
    8

    Angry

    Aarrggghhh.....

    I'd rather gouge my eye out with a spoon than deal with MS tech support.

    But I guess desperate needs require desperate deeds.

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Being ACCESS ADP is involved the one and only option would be to contact MS PSS, as there were changes to ACCESS and SQL recently.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Feb 2003
    Posts
    109

    adp

    if you are worried about views, rewrite your views in order to open in less than 5 minutes. this setting is under <TOOLS><OPTIONS>

    if you are worried about executing lengthy tsl code/stored procs (without viewing the data in a datasheet) you should write your own handler similiar to this one:

    Public Sub runSql(sql As String)
    On Error GoTo errhandler

    debug.print " " & sql

    Dim ReturnErrDescription
    Dim strLogSql
    Dim strRunSql
    Dim strSql As String
    Dim recordCount As Integer

    'this just logs the sql statement-- crucial for complex adp applications
    strLogSql = "EXEC PerfMngmtCodeFunction.dbo.uspLogSql '" & Replace(sql, "'", "''") & "'"
    'docmd.runSql strLogSql

    Dim ADOCmd As ADODB.Command
    Set ADOCmd = New ADODB.Command

    With ADOCmd
    .CommandText = sql
    .ActiveConnection = CurrentProject.Connection
    .CommandTimeout = 0
    .Execute
    End With

    Set ADOCmd = Nothing
    cleanexit:
    Exit Sub
    errhandler:
    Select Case err.Number
    Case Else
    Msgbox err.Number & " - " & err.Description, vbOKOnly
    Resume Next
    Resume cleanexit
    End Select
    End Sub
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  10. #10
    Join Date
    Apr 2003
    Posts
    8

    Cool

    Thanks for the replies. I thought I would post this to let everyone know I found a workaround, in case this happens to someone else.

    I created an Excel spreadsheet, then added a form with a list box that I loaded with all the view names. When the user selects a view and clicks the GO button on the form, I establish a connection, then open a recordset on that connection with the timeout set to 0. Once the recordset returns, I execute a nifty little command in Excel that loads the spreadsheet from the recordset:

    ActiveSheet.Range("A2").CopyFromRecordset rsView

    I then finish it off with some code that loads stuff into column 1 and voila!

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sounds like a hack solution. Have you tried running ALTER VIEW to clear out the procedure cache? Why don't you post one of the offending views for review?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •