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

[Erland Sommarskog]

(te********@hotmail.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****@sommarskog.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

[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********@hotmail.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.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