Menu
Hi, I am maintaining some old ASP code that I did not write. I am a programmer and a DB admin but I am not very familiar with ASP. Here is the situation: I have a web page that runs a query to SQL 2005.
If the query returns 5 or 10 records it's fine. If it returns a large number, (1000), then I get the following error message on my browser: - Microsoft OLE DB Provider for ODBC Drivers error '80040e31' MicrosoftODBC SQL Server DriverTimeout expired /utility/searchscript.asp, line 201 - LINE 201 is below. Set UserNames = Server.Createobject('ADODB.Recordset ') LINE 201 - UserNames.Open sqlq, Connect,adopenstatic, adlockreadonly If I run the same query in a SQL command line I get no problems returning the data.
So my question is: Is the timeout issue an ASP script problem or an ODBC problem? Suggestions please on how to fix this. The entire script is about 400 lines long, which is why I didn't post the whole thing.
But I can post additional pieces of it if need be.
Microsoft OLE DB Provider for ODBC Drivers (0x80004005) MicrosoftODBC SQL Server DriverTimeout expired o n the ADODB.Connection.Open On the.NET2 apps we log Message Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. StackTrace at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection.ctor(SqlPartitionInfo sqlPartitionInfo) Source.Net SqlClient Data Provider. For.NET via TCP/IP The regular ASP websites are having problems as well though using ODBC.
Upping the timeout seems like a band-aid at best and may lead to connections piling up and making the situation worse. There isn't a good reason for communication between the frontends and db server over a private Gigabit link to take over 30 seconds to connect. You mentioned there should be no reason for the updates to timeout, if that was true there would be no timeouts. You need to re-evaluate your understanding of what's happening under each transaction. I would suggest you run SQL profiler during medium to medium-high volume periods. Use small sample windows to keep from creating big profile logs.
And then review the duration and Disk I/O for ALL activity during the sample preriod. You are looking for transaction or events which trend to last a lot longer or cause a lot more Disk I/O than the rest of your transactions. At the same time you are capturing the profile, look for transactions being blocked, you run an 'spwho2' on a query window or use the Activity Monitor in the SQL Management Studio, under Management.
See More On Stackoverflow
You should be able to identify one or several transaction which are involved in the particular timeout. Understand what is being updated or read, how many records, which records, lock types. Check for triggers which may be trying to access the same resource or remote resource. Do the leg work. You will find the root cause.
Happy hunting! Typically, connection timeouts cannot be logged on the server, as the problem is that the client cannot connect to the server. From the perspective of the server, nothing happened. That is one of the reasons that these are difficult to figure out.
General network error is similarly difficult, as it appears to both the server and client as though the other party disconnected (though it was actually disconnected somewhere in-between). Targetting the faulty network hardware or cables can be very difficult if you don't look to logs on your routers. I wish you both luck, John.
To add some more details on John's recommendations: Connection timeouts alone can indicate some long-running query issues, but when the timeout occurs in less time than it should, or especially when it is accompanied by general network errors, there is almost certainly an issue in the networking hardware. As John says, these problems are hard to troubleshoot; both the client and the server think that the other is to blame for the issue, because the problem is in the middle, so they can't really provide much useful information.
One step you can take to verify that the problem is in the networking hardware is to setup a Netmon trace on both the client and server machines, and look for the TCP connection reset. You should see the connection reset arriving on either the client or the server, and you will also see that the other side never sent a connection reset; this indicates that something in the middle layer decided to reset the connection. Off the top of my head, the particular problematic networking hardware could be: a faulty router, a faulty cable, a NIC with a faulty TOE implementation, or anything else that may be handling traffic between the client and server.
. Find product guides, documentation, training, onboarding information, and support articles. Submit a ticket for technical and product assistance, or get customer service help. Learn through self-study, instructor-led, and on-demand classes with the SolarWinds Academy. Download the latest product versions and hotfixes. Manage your portal account and all your products.
Give More Feedback
Renew to download the latest product features, get 24/7 tech support, and access to instructor-led training.
I solved this problem by recreating the stored procedure! Just a simple DROP and then CREATE stopped the timeouts in my case! I had suffered with this problem for a week; a classic ASP said 'SQL timeout' when I could run the same query direct on the database in less than a second. (I did not see the 'does not exist' message though.) The ASP had been working fine for a whole month. A genius friend of mine said, 'Going through ASP uses a 'cached' execution plan, which is not very efficient. Try dropping and recreating. This does suggest your stored procedure could do with re-writing to make it more efficient, as it could happen again.'
Since the proc was running fine when tested with SQL Management Studio, I assume that does not used the cached plan, but ASP does.
Take on the role of a warehouse worker, loading and moving goods using all original forklifts. Forklift Truck Simulator release date for PC: game language: English. 16 February 2009| Worldwide. Get behind the controls of a forklift truck in this highly. Take on the role as a forklift truck driver and take your place in these highly detailed vehicles. In “Forklift Truck Simulator 2009” your task is to master. Game forklift truck simulator 2009.