Database Timeout

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 dont think its a problem with the query. It just because the time taken to go run the query takes longer than a setting somewhere.

Ive tried changing the ODBC timeout settings on my PC and the driver on the server which didnt work

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

Thanks for any advice

Re: Database Timeout

Can you please share the query which generates error ?

Re: Database Timeout

try increasing connection and command timeout

Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80

timeout value in second here

hope this helps...

Re: Database Timeout

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

Re: Database Timeout

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

Re: Database Timeout

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

Re: Database Timeout

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

Re: Database Timeout

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!

Re: Database Timeout

danieldunn10

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

Re: Database Timeout

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

Re: Database Timeout

Another error message using a different browser is

Sys.WebForms.PageRequestManagerTimeoutException

Does that help?

Thanks

Re: Database Timeout

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

Re: Database Timeout

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?

Re: Database Timeout

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!

Re: Database Timeout

set connectiontimeout to some higher value... like 100

MyConnection.ConnectionTimeout = 100

hope this helps...

Re: Database Timeout

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

Re: Database Timeout

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

Re: Database Timeout

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