ODBC Timeout problems but very hard to pin down

teddysnips

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.

Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).

APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)

All connect to the same database server - different databases, natch.

1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace

APP 1 - ACCESS APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. Open a Search form and the default recordset
is displayed correctly.
NOTE: This SQL captured on trace.

2. Enter a criterion into one of the controls on the search form and
press "Filter". Wait a minute and watch the hourglass. Eventually
ODBC Timeout error displayed.
NOTE: This SQL captured on trace.

3. Right-click on the appropriate column on the sub-form and enter
the same criterion as for step 2 above. NO ODBC TIMEOUT - CORRECT
RECORDSET RETURNED WITHIN TWO SECONDS
NOTE: This SQL captured on trace.

NOTE: If I open the (linked) tables in the Microsoft Access database
pane I can manipulate the data at will. However, if I try to open any
stored queries in Access they time out.

APP 2 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display for the
logged-on user contains no records.
2. Change the parameters to allow some records to be displayed.
Watch the progress bar do nothing and then the system displays the
"Object reference not set to an instance of an object" exception.
NOTE: This SQL captured on trace.

APP 3 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display contains
no records.
2. Attempt to add a new record. System pretty slow.
3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.

If the above wasn't weird enough for you, get this.

If I cut and paste the SQL that's being captured by the Profiler at
each point and paste it into Query Analyser, it totally barfs. I
waited 11 minutes for one query but it just kept running, never
returning. EVEN THE QUERY AT APP 1 POINT 3.

Needless to say, if I run these queries back at the office it all goes
according to plan.

I think they've got a problem.....

Edward

Aug 16 '07 #1

Subscribe Reply

2356

Erland Sommarskog

(te********@hot mail.com) writes:

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.

They always say that. :-)

Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).

APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)

All connect to the same database server - different databases, natch.

1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace

As long as you only talk about the first two applications, the symptom
is not unknown. Keep in mind that the optimizer builds the query plans
based on estimates from statistics sampled about the data. There are
two different ways that this can go wrong:

1) The plans falls out of cache for some reason; someone runs a huge query,
or the server is restared. The queries are such that they ask for rows
added the last few days, but statistics have not been updated to reflect
this, so the optimizer thinks there are very rows when in fact there are
very many, and picks a bad plan.

2) Statistics are auto-updated, and the plans are recompiled in reaction
to this. The new data causes the optimizer to pick a new plan, which
unfortunately is not as the good as the old one.

The first of these two cases are easiest to address: make sure that
statistics for the updated tables are up to date. The second case may
require tweaking the query and possible adding an index hint.

But from what happens here:

3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.

It appears that someone has loaded a lot of data. Of course, this can affect
both performance and results.

I get a little nervous when you say that you have each item four times in
the lookup tables. Doesn't these tables have a primary key?

It could be that they have a corruption and you could run DBCC CHECKDB to
find out. But I'm quite willing to bet that the corruption they have is
on application level. That is, someone has loaded data he shouldn't have
loaded.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 16 '07 #2

sloan

ODBC? What year is it?

If you have Access and/or Sql Server, there's not really a good reason to
use ODBC.

...

Change to the other providers, and heck, it'll start working faster, and
you'll be a hero.

<te********@hot mail.comwrote in message
news:11******** **************@ d55g2000hsg.goo glegroups.com.. .

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.

Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).

APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)

All connect to the same database server - different databases, natch.

1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace

APP 1 - ACCESS APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. Open a Search form and the default recordset
is displayed correctly.
NOTE: This SQL captured on trace.

2. Enter a criterion into one of the controls on the search form and
press "Filter". Wait a minute and watch the hourglass. Eventually
ODBC Timeout error displayed.
NOTE: This SQL captured on trace.

3. Right-click on the appropriate column on the sub-form and enter
the same criterion as for step 2 above. NO ODBC TIMEOUT - CORRECT
RECORDSET RETURNED WITHIN TWO SECONDS
NOTE: This SQL captured on trace.

NOTE: If I open the (linked) tables in the Microsoft Access database
pane I can manipulate the data at will. However, if I try to open any
stored queries in Access they time out.

APP 2 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display for the
logged-on user contains no records.
2. Change the parameters to allow some records to be displayed.
Watch the progress bar do nothing and then the system displays the
"Object reference not set to an instance of an object" exception.
NOTE: This SQL captured on trace.

APP 3 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display contains
no records.
2. Attempt to add a new record. System pretty slow.
3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.

If the above wasn't weird enough for you, get this.

If I cut and paste the SQL that's being captured by the Profiler at
each point and paste it into Query Analyser, it totally barfs. I
waited 11 minutes for one query but it just kept running, never
returning. EVEN THE QUERY AT APP 1 POINT 3.

Needless to say, if I run these queries back at the office it all goes
according to plan.

I think they've got a problem.....

Edward

Aug 16 '07 #3

Similar topics

6771

Bad performance in queries with Jet4.0 and linked ODBC-tables to SQL-Server 2000

by: Andreas Lauffer | last post by:

I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:

Microsoft SQL Server

2805

session timeout suggestions wanted

by: Craig Storey | last post by:

I have a form where users logged in using sessions can edit articles in a WYSIWYG editor. Some of them take their time and don't like to save their work very often and occassionally the sessions expire and their work goes poof. (I've suggested editing off-line and simply copy+paste but they prefer the editor.) After a lot of searching I've found the main culprit is session.gc_maxlifetime and I can set it longet like... ...

PHP

7021

ODBC Call Failed

by: Jimbo | last post by:

I have a form in access 97 that is populated by a query against some sql server tables....when ever the query pulls up only one record im fine..however if it pulls up more than one record i have to make sure i click the the navigation arrows through all the records or else I get an odbc error and all the fields go to #Name? If I click the arrows and scroll through all the records Im ok.. What is causing this ?

Microsoft Access / VBA

4583

System.Data.SqlClient "Timeout expired" causing ASP.net web application to automatically restart.

by: Chris Langston | last post by:

I have a Web Server running IIS 5 or 6 on Windows 2K and Windows 2003 Server that is experiencing strange shutdown problems. We are using ASP.NET v1.1 and our application is written in VB.NET Here's the scenario: 1. .NET Windows Client on a remote machine makes a web service call to update tables on a Web Server running SQL Server 2000. 2. The Update is updating about 1000 - 3000 records doing simple update statements like "Update...

ASP.NET

1108

HELP! Timeout when accessing Webservice.

by: RichardF | last post by:

I have a solution that contains a web service and a web site. I was working on it all day Monday with no problems. Tuesday morning it stopped working, but ONLY when run from inside Visual Studio (VS). Now, when I run it from inside VS, my web site times out when trying to access the web service. The initial login page comes up OK so I enter my user name and password and hit enter, at that time the web site attempts to contact the web...

ASP.NET

19398

PHP redirect timeout

by: Dave | last post by:

Hi All, Can anyone suggest a reliable solution to php redirect?: header("Location: $redir_url"); if the above times out in $timeout seconds header("Location: $alt_redir_url"); Many thanks,

PHP

11753

Change serial timeout per read

by: rowan | last post by:

I'm writing a driver in Python for an old fashioned piece of serial equipment. Currently I'm using the USPP serial module. From what I can see all the serial modules seem to set the timeout when you open a serial port. This is not what I want to do. I need to change the timeout each time I do a "read" on the serial port, depending on which part of the protocol I've got to. Sometimes a return character is expected within half a second,...

Python

10078

DB2 and ODBC - Function sequence error

by: Szymon Dembek | last post by:

Hi Recently I did some DB2 and ODBC coding in Visual FoxPro. I bumped on a problem I cannot resolve. When I issue a delete statement that deletes no rows (no rows qualify for the WHERE conditions), DB2 raises SQL0100W warning with SQLSTATE=02000. The same happens when issuing an insert with select clause (insert into .... select from ...) when no rows are fetched by the select clause.

DB2 Database

6628

ODBC Timeout problems but very hard to pin down

by: teddysnips | last post by:

Microsoft SQL Server

4526

Re: TcpClient read/write timeouts do not timeout

by: Zytan | last post by:

I just had the problem occur again, with NetworkStream.Write() doing its thing with a timeout... and it just sits and waits and waits and waits... it never times outs. So, I shut the server down just to see if THAT will make it at least end the function call and continue (since it's a synchronous call, so the program is delayed until it returns), and even THAT doesn't make the call end. It just sits and waits... Even IF I have a bug...

C# / C Sharp

8675

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8604

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9160

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8897

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

6521

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5860

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4370

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

3050

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2331

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP