473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

ODBC Timeout problems but very hard to pin down

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
4 1324
<te********@hotmail.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...
I think they've got a problem.....
Yes indeed - they're using ODBC...

Why...?
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Aug 16 '07 #2
Tuesdays are Microsoft update days. Did your server(s) perform any
automatic Microsoft updates?
Ross

<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


Aug 16 '07 #3
(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
Aug 16 '07 #4

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

Aug 16 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
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...
3
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...
8
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...
2
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 ...
0
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...
5
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,
4
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...
4
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...
2
by: teddysnips | last post by:
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...
2
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.