Database Timeout

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

Answer 1

User2105156359 posted

hi

Can you please share the query which generates error ?

Answer 2

User551462331 posted

try increasing connection and command timeout

Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80

timeout value in second here

hope this helps...

Answer 3

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

Answer 4

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

Answer 5

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...

Answer 6

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

Answer 7

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!

Answer 8

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

Answer 9

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

Answer 10

User1225369314 posted

Another error message using a different browser is

Sys.WebForms.PageRequestManagerTimeoutException

Does that help?

Thanks

Answer 11

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...

Answer 12

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?

Answer 13

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!

Answer 14

User551462331 posted

 set connectiontimeout to some higher value... like 100

MyConnection.ConnectionTimeout = 100 

hope this helps...

Answer 15

User1225369314 posted

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

Answer 16

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 

 

Answer 17

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