locked
Database Timeout RRS feed

  • Question

  • User1225369314 posted

    Hi, I have a sql query which works fine with one table, when I join two more tables to the query it comes up with this error though.

    “ERROR [HYT00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Timeout expired.
    ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Query aborted on user request (7495)"

    It does work sometimes though, I don’t think its a problem with the query. It just because the time taken to go run the query takes longer than a setting somewhere.

    I’ve tried changing the ODBC timeout settings on my PC and the driver on the server which didn’t work

    When I run the exact same query in MS Access it works fine

    Thanks for any advice

    Tuesday, March 13, 2012 5:42 AM

All replies

  • User2105156359 posted

    hi

    Can you please share the query which generates error ?

    Tuesday, March 13, 2012 5:54 AM
  • User551462331 posted

    try increasing connection and command timeout

    Connection.ConnectionTimeout = 30
    Connection.CommandTimeout = 80

    timeout value in second here

    hope this helps...

    Tuesday, March 13, 2012 6:00 AM
  • User1225369314 posted

    Thanks KK, I get this error now!

    ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

    I will post my Query in a moment

    Thank You

    Tuesday, March 13, 2012 6:10 AM
  • User1225369314 posted
            Dim sql, dbcomm As Object
            Dim Billing As Double
    
            Dim MyConnection As System.Data.Odbc.OdbcConnection
    
            MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;DB=sosdb;UID=dd;PWD=dantheman;HOST=newfileserver;PORT=2511")
            
           'MyConnection.ConnectionTimeout = 80
    
            MyConnection.Open()
    
            sql = "SELECT ""DEPARTMENT-DESCRIPTION"", "
    
            sql += " Sum( "
            sql += " CASE "
            sql += " WHEN ""POST-DATE"" BETWEEN CURDATE()-365 AND CURDATE() THEN ""CURR-VALUE"" "
            sql += " END) AS CurrentValue "
    
            sql += " FROM (PUB.TRTRANS LEFT JOIN PUB.FEETR ON PUB.TRTRANS.""FEE-EARNER"" = PUB.FEETR.""FEE-EARNER"") LEFT JOIN PUB.DEPARTMENT ON PUB.FEETR.DEPARTMENT = PUB.DEPARTMENT.""DEPARTMENT-CODE"" "
    
            sql += " WHERE ( (""ACT-CODE"" = 'BILL' OR ""ACT-CODE"" = 'CN') AND UNDONE = 0 AND ""DEPARTMENT-DESCRIPTION"" = '" + fee_earner.SelectedValue + "' AND ""CURR-VALUE"" > 0 )"
    
            sql += " GROUP BY ""DEPARTMENT-DESCRIPTION"" "
    
    
            dbcomm = New System.Data.Odbc.OdbcDataAdapter(sql, MyConnection)
    
            Dim dt As DataTable = New DataTable()
            dbcomm.Fill(dt)
    
            MyConnection.Close()

    I've tried taking the brackets out of the FROM clause which works on other queries but i get the same thing

    This is a picture of the join in Access which works - https://skydrive.live.com/redir.aspx?cid=9c2775bd3b48974e&resid=9C2775BD3B48974E!116&parid=9C2775BD3B48974E!114

    Thanks

    Tuesday, March 13, 2012 6:18 AM
  • User551462331 posted

    have u created the DSN on u r machine? is it named 2501? seems there is issue with coneection string... check this

    http://www.connectionstrings.com/dsn

    hope this helps...

    Tuesday, March 13, 2012 6:45 AM
  • User1225369314 posted

    The DSN is on the server where the files are in IIS.

    Yep the DSN name is 2501

    I've used that connection string in many other apps to connect to the database. Sould I change it?

    Thanks

    Tuesday, March 13, 2012 6:55 AM
  • User1225369314 posted

    I've got the page to load for the first time using this!!

            MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;UID=dd;PWD=dantheman")
            MyConnection.ConnectionTimeout = 80

    I will check to see if it works when I change the rest

    Thanks very much!

    Tuesday, March 13, 2012 7:04 AM
  • User551462331 posted

    I've got the page to load for the first time using this!!

            MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;UID=dd;PWD=dantheman")
            MyConnection.ConnectionTimeout = 80

    do u mean it is working now? or still same issue

    Tuesday, March 13, 2012 7:23 AM
  • User1225369314 posted

    I think i still have the problem sorry

    It does work if you refresh the page 5 or 6 times.

    It seems to work sometimes and others not!?

    Thank you

    Tuesday, March 13, 2012 7:47 AM
  • User1225369314 posted

    Another error message using a different browser is

    Sys.WebForms.PageRequestManagerTimeoutException

    Does that help?

    Thanks

    Tuesday, March 13, 2012 8:12 AM
  • User551462331 posted

    seems, you are using updatepanel on u r page where retriving data...

    this makes it as asynchronous operation.... other than query execution time, this async operation of updatepanel is also getting timed out

    to avoid this, increast asynchpostbacktimeout of scriptmanager

    find the <asp:scriptmanager... > tag on the aspx page and increase this

    <asp:ScriptManager AsyncPostBackTimeout="100" ... >

    hope this helps...

    Tuesday, March 13, 2012 8:21 AM
  • User1225369314 posted

    Thanks for that, I think that might help when I use the dropdown menu to update the page. I'm still getting the problem when loading up the page first time sorry.

            Dim sql, dbcomm As Object
            Dim Billing As Double
    
            Dim MyConnection As System.Data.Odbc.OdbcConnection
    
            MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;UID=dd;PWD=dantheman")
            MyConnection.ConnectionTimeout = 0
    
            MyConnection.Open()
    
            sql = "SELECT ""DEPARTMENT-CODE"", "
    
            sql += " Sum( "
            sql += " CASE "
            sql += " WHEN YEAR = '2011' THEN ""CURR-VALUE"" "
            sql += " END) AS CurrentValue "
    
            sql += " FROM PUB.TRTRANS LEFT JOIN PUB.FEETR ON PUB.TRTRANS.""FEE-EARNER"" = PUB.FEETR.""FEE-EARNER"" LEFT JOIN PUB.DEPARTMENT ON PUB.FEETR.DEPARTMENT = PUB.DEPARTMENT.""DEPARTMENT-CODE"" "
    
            sql += " WHERE ( (""ACT-CODE"" = 'BILL' OR ""ACT-CODE"" = 'CN') AND UNDONE = 0 AND ""CURR-VALUE"" > 0 )"
    
            sql += " GROUP BY ""DEPARTMENT-CODE"" "
            sql += " HAVING ""DEPARTMENT-CODE""  = '" + fee_earner.SelectedValue + "' "
    
            dbcomm = New System.Data.Odbc.OdbcDataAdapter(sql, MyConnection)
    
            Dim dt As DataTable = New DataTable()
            dbcomm.Fill(dt)
            MyConnection.Close()

    How could I use CommanTimeout, do you think that is whats missing?

    Tuesday, March 13, 2012 8:52 AM
  • User1225369314 posted

    I'm now trying

            Dim sql, dbcomm As Object
            Dim Billing As Double
    
            Dim MyConnection As System.Data.Odbc.OdbcConnection
    
            MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;UID=dd;PWD=dantheman")
            MyConnection.ConnectionTimeout = 0
    
            MyConnection.Open()
    
            Dim cmd As OdbcCommand
            cmd = MyConnection.CreateCommand()
    
            sql = "SELECT ""DEPARTMENT-CODE"", "
    
            sql += " Sum( "
            sql += " CASE "
            sql += " WHEN YEAR = '2011' THEN ""CURR-VALUE"" "
            sql += " END) AS CurrentValue "
    
            sql += " FROM PUB.TRTRANS LEFT JOIN PUB.FEETR ON PUB.TRTRANS.""FEE-EARNER"" = PUB.FEETR.""FEE-EARNER"" LEFT JOIN PUB.DEPARTMENT ON PUB.FEETR.DEPARTMENT = PUB.DEPARTMENT.""DEPARTMENT-CODE"" "
    
            sql += " WHERE ( (""ACT-CODE"" = 'BILL' OR ""ACT-CODE"" = 'CN') AND UNDONE = 0 AND ""CURR-VALUE"" > 0 )"
    
            sql += " GROUP BY ""DEPARTMENT-CODE"" "
            sql += " HAVING ""DEPARTMENT-CODE""  = '" + fee_earner.SelectedValue + "' "
    
            cmd.CommandText = sql
            cmd.CommandTimeout = 80000000
    
            dbcomm = New System.Data.Odbc.OdbcDataAdapter(sql, MyConnection)
    
            Dim dt As DataTable = New DataTable()
            dbcomm.Fill(dt)
    
            MyConnection.Close()

    Still not there yet though!

    Tuesday, March 13, 2012 9:51 AM
  • User551462331 posted

     set connectiontimeout to some higher value... like 100

    MyConnection.ConnectionTimeout = 100 

    hope this helps...

    Tuesday, March 13, 2012 9:54 AM
  • User1225369314 posted

    yep, ive tried really high numbers like 100000...

    Tuesday, March 13, 2012 10:14 AM
  • User551462331 posted

    I ma referring to ConnectionTimeout... in the last code posted by you, it is 0

            Dim MyConnection As System.Data.Odbc.OdbcConnection 
     
           
    MyConnection = New System.Data.Odbc.OdbcConnection("DSN=2501;UID=dd;PWD=dantheman") 
           
    MyConnection.ConnectionTimeout = 0 

     

    Tuesday, March 13, 2012 10:26 AM
  • User1225369314 posted

    Hi, I just noticed I had a reply sorry.

    In between posts, I was trying many different numbers 0, 100, 1000, 10000, 100000

    I couldnt get any of them to work...

    Thanks for any advice

    Monday, March 19, 2012 8:15 AM