I am working on an ASP.net 2.0 application written in C#, pulling data from an Oracle 10g database on a remote server (i.e. the application and database are on different servers).

The data is pulled via a SqlDataSource control. When I run small, quick queries, everything works fine, but when I try to run a query that returns 3000-4000 records, I get the error message in the subject line. The query times out after about 15 seconds. I have taking the exact query that my application is trying to run, and ran it directly on the database server. It takes a few seconds, but it works. I have reached the conclusion that it must be a timeout issue.

I have pored through many forums, and based on my findings, tried 4 fixes:

1) Going into the control panel, then administrative tools, then ODBC sources. I selected the appropriate DSN, and unchecked the "Enable Query Timeout" option.

2) Added the connection lifetime and connection timeout options to the connection string as follows:

sql.ConnectionString = "DSN=******;Connection Lifetime=0;Connection Timeout=0;UID=****;PWD=****; DRIVER={Microsoft ODBC for Oracle}; SERVER=***.***.**.*;";

3) I have added the line "SQLNET.EXPIRE_TIME = 0" to every one of the three sqlnet.ora files on the database server.

4) An old coworker of mine used "Server.ScriptTimeout = 600" in his code. But, these were plain old ASP pages.

None of these things worked. Please help!!!

Recommended Answers

All 3 Replies

I ended up just splitting large queries into smaller subqueries.

" Going into the control panel, then administrative tools, then ODBC sources. I selected the appropriate DSN, and unchecked the "Enable Query Timeout" option."

If your connection string says QTO=T (for query time out = TRUE) somewhere in it then this will over ride the setting in the DSN. And the result will be that long queries will still generate ORA-01013:user requested cancel of current operation

I'd also switch to the Oracle ADO.NET provider instead of using the ODBC one. It's been optimized for Oracle access.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.