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

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

Jump to Post

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 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.