ODBC call failed - weird behavior

Hello,
I have been working on updating an Access 2000 application that uses linked
ODBC tables to connect to an SQL Server 2000. My development machine is
connected to the server over a Microsoft LAN, at 100Mbits.
Both the Office 2000 on my development machine and the SQL server has all
the latest SP's
Everything worked fine for months until today. I am not aware of any changes
to ether machine since yesterday.
Now, there is one table that can not be opened any more.
I can access all the other tables ecxept that one. When I try to open it I
get the message "ODBC call failed". The table is displayed but with #Name?
in all columns for all records.
Once that has happened I get the same message for all tables. In addition to
"ODBC call failed" there is also
"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
When I close down and reopen the app I can again access all tables except
the bad one.
At first, I thought the table had gotten corrupted, but I can open the table
just fine on the server. If I set up an ODBC on the server with the same
name I and run my application there. the problem table opens fine.
I removed the ODBC and set it up again with the same properties, client uses
TCP/IP network library, and I am using SQL Server authentication.
I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
I ran
I am wondering if I should update to 2.8
If anyone has a suggestion I would appreciate it very much.
RagnarIt could be an MDAC issue. You could run the MDAC component
checker on a machine where the app works and another machine
where the app gets the ODBC error and see if there are any
differences in MDAC versions. You can get component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
You could also turn on ODBC tracing then reproduce your
error and then turn tracing off again. You could open up the
ODBC trace log, look for specifics of what's failing and try
to track down the issue from there.
-Sue
On Wed, 10 Mar 2004 19:05:29 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:

>Hello,
>I have been working on updating an Access 2000 application that uses linked
>ODBC tables to connect to an SQL Server 2000. My development machine is
>connected to the server over a Microsoft LAN, at 100Mbits.
>Both the Office 2000 on my development machine and the SQL server has all
>the latest SP's
>Everything worked fine for months until today. I am not aware of any change
s
>to ether machine since yesterday.
>Now, there is one table that can not be opened any more.
>I can access all the other tables ecxept that one. When I try to open it I
>get the message "ODBC call failed". The table is displayed but with #Name?
>in all columns for all records.
>Once that has happened I get the same message for all tables. In addition t
o
>"ODBC call failed" there is also
>"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
>When I close down and reopen the app I can again access all tables except
>the bad one.
>At first, I thought the table had gotten corrupted, but I can open the tabl
e
>just fine on the server. If I set up an ODBC on the server with the same
>name I and run my application there. the problem table opens fine.
>I removed the ODBC and set it up again with the same properties, client use
s
>TCP/IP network library, and I am using SQL Server authentication.
>I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
>I ran
>I am wondering if I should update to 2.8
>If anyone has a suggestion I would appreciate it very much.
>Ragnar
>|||Thank you Sue,
I had run ComponentChecker on my development machine and it now has 2.6 SP2
Refresh wih a couple of files being slightly newer than expected, ODBC32.dll
and ODBCCP32.dll.
The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
expected, including the ODBC32.dll and ODBCCP32.dll.
I thought I had run a trace, but I did not realize I had to switch it on. I
ran a trace and found these error lines toward the end. This trace was only
running while I opened the problem table in the DB window.
MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 09022400
DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionRead (recv()). (10054)
DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sock
ets]General
network error. Check your network documentation. (11)
I am not familiar with ODBC calls, but I see there were two error messages
I have done some more testing and found that I can open a form that displays
the contents of this table in a listbox with no errors. The RowSource for
the listbox is a select query that selects all fields of the table.
Yet, if I create a stand alone query that does the same, it fails.
I am beginning to suspect network problems, possibly the NIC on the server.
We had problems with that before and it was replaced. The connection into
the main network from my machine also goes through a switch, which could be
acting up.
I still think I am going to update MDAC to the same as the server, or do you
think 2.8 would be better?
Ragnar|||You'd definitely want to be on 2.7 SP1 at least due to all
of the bug fixes for it since 2.6 SP2. I'd try to go that
route first being that it works on your server.
2.8 has a lot of security changes and it should be tested in
another environment if possible before installing. It's the
base mdac version for Windows 2003 but I don't think
anything other products, service packs, etc are installing
it at this point.
The list box testing you did is odd, otherwise I'd suspect
network issues as well (or wouldn't rule them out anyway).
Were both of the connections, data source properties for the
list box and the stand alone query exactly the same?
-Sue
On Thu, 11 Mar 2004 13:13:12 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:

>Thank you Sue,
>I had run ComponentChecker on my development machine and it now has 2.6 SP2
>Refresh wih a couple of files being slightly newer than expected, ODBC32.dl
l
>and ODBCCP32.dll.
>The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
>expected, including the ODBC32.dll and ODBCCP32.dll.
>I thought I had run a trace, but I did not realize I had to switch it on. I
>ran a trace and found these error lines toward the end. This trace was only
>running while I opened the problem table in the DB window.
>MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
> HSTMT 09022400
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
>Sockets]ConnectionRead (recv()). (10054)
> DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP S
ockets]General
>network error. Check your network documentation. (11)
>I am not familiar with ODBC calls, but I see there were two error messages
>I have done some more testing and found that I can open a form that display
s
>the contents of this table in a listbox with no errors. The RowSource for
>the listbox is a select query that selects all fields of the table.
>Yet, if I create a stand alone query that does the same, it fails.
>I am beginning to suspect network problems, possibly the NIC on the server.
>We had problems with that before and it was replaced. The connection into
>the main network from my machine also goes through a switch, which could be
>acting up.
>I still think I am going to update MDAC to the same as the server, or do yo
u
>think 2.8 would be better?
>Ragnar
>|||Thank you Sue,

> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.

> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar|||Hi, I had a similar issue with an Oracle database connection. I got around
it by removing the linked tables, compacting/repairing the Access database a
nd recreating the links. I suspect that Access is maintaining some informat
ion about the back-end tables that is not visible and is corrupt. If so, th
is information is removed by the repair if the link is gone. Anyway, hopefu
lly this will also work for you.
... Bob

quote:

---

Originally posted by Ragnar Midtskogen
Thank you Sue,

> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.

> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar

---

Occasional "Timeout expired" message - on SP that should take 1 second

Hi there,
I've got something kind of weird going on. I have a stored procedure that
normally takes less than one second to execute, but it has started taking
over 30 seconds from time to time.
The SP is called by an ASP page on a web server, running on a separate
machine. The database server is running SQL Server 2000. The ASP page
makes an ODBC connection, using TCP/IP and creates & sends the stored
procedure call.
From time to time, something goes haywire, and the ASP page returns
[Microsoft][ODBC SQL Server Driver]Timeout expired
Then, the ASP page will not work no matter what we do, even if we leave it a
day or two. (No locks on the database, sometimes not even any connections!)
The only way to fix it is to run the stored procedure through Query
Analyzer. It will take a little over 30 seconds, but finish successfully.
Then, if I run the exact same SP again, it will complete in 0 seconds, and
the web page will work fine again.
I've seen this before, but I cannot for the life of me remember what we did
to fix it. We tried changing the ODBC connection to Named Pipes instead of
TCP/IP but that didn't help.
Any suggestions?
BeverleyI am having the same issue and the solution provided by SQL crafter in the S
ql Server Timeout expired post is not working. Could someone help us with t
his issue?
Thanks,
Rick|||I have been struggling with a similar problem for a couple of weeks and came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes, ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- Fred
"Beverley" <ali_webitems@.hotmail.com> wrote in message
news:OpxbH$K9DHA.712@.tk2msftngp13.phx.gbl...
> Hi there,
> I've got something kind of weird going on. I have a stored procedure that
> normally takes less than one second to execute, but it has started taking
> over 30 seconds from time to time.
> The SP is called by an ASP page on a web server, running on a separate
> machine. The database server is running SQL Server 2000. The ASP page
> makes an ODBC connection, using TCP/IP and creates & sends the stored
> procedure call.
> From time to time, something goes haywire, and the ASP page returns
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Then, the ASP page will not work no matter what we do, even if we leave it
a
> day or two. (No locks on the database, sometimes not even any
connections!)
> The only way to fix it is to run the stored procedure through Query
> Analyzer. It will take a little over 30 seconds, but finish successfully.
> Then, if I run the exact same SP again, it will complete in 0 seconds, and
> the web page will work fine again.
> I've seen this before, but I cannot for the life of me remember what we
did
> to fix it. We tried changing the ODBC connection to Named Pipes instead
of
> TCP/IP but that didn't help.
> Any suggestions?
> Beverley
>|||I have been struggling with a similar problem for a couple of weeks and
came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred|||ITFRED, have you found a solution to your problem at all'
I have a similar problem, I also get the *occasional timeout*.
Mine is an ASP web application connected to MSDE, and the application
gets a timeout error occasionally when the ASP code tries to update
many records in a table, but smaller tables work fine.
The error message is:
> Error Number -214xxxxxx, ODBC SQL Server Driver timeout expired.
In a testing environment on a dev server, the same ASP application
connects to a SQL2K database and it has NOT encountered any problems.
My conclusion is (thanks to all your posts I have read) that it is in
ODBC connections where the MSDE engine times out.
Can anyone please come up with a solution!'
Is using a different database an option, perhaps mysql or postgreSQL?
Thanks.
huge
ITFred wrote:
> *I have been struggling with a similar problem for a couple of weeks
> and
> came
> to this group in search of a solution. Perhaps a pooling of
> information
> will help.
> In my situation, I have a VB interactive front-end on multiple
> machines
> accessing a SQL2K database through ODBC over TCP/IP. It has been in
> service
> for over 2 years and the master table now has over 750K records in
> it.
> Recently we started receiving occasional "Timeout Expired" errors in
> pretty
> much the same manner as you describe -- a query that would normally
> take
> 1-2 seconds would suddenly take over 90 and crap out. I ran
> database
> integrity checks, reconstructed the indexes, created additional
> indexes,
> ran
> numerous original and reconfigured queries through the Index
> Analyzer. And
> of course spent hours trying various searches in the MS KB. I have
> reached
> the following conclusions:
> The problem (in my case, at least) originates with the Query
> Optimizer. A
> very specific query run through the Query Analyzer will *always*
> select an
> appropriate index or mix of indexes. The *identical* query
> submitted
> through ODBC, however, will sometimes select a completely
> inappropriate
> index mix, or no index at all, wind up executing full-table scans or
> other
> time-wasting substitutes, and time out. This effect, I suspect, is
> a
> permanent feature of MSSQL, but does not manifest on smaller tables
> because
> the malfunction does not cause a timeout on smaller tables, just an
> inexcusable waste of time.
> I proved this theory by adding index hints to my ODBC-originated
> queries
> (which Books Online says should never be necessary), and observing
> the
> performance stabilize. Problem is, I consider this a completely
> hokey
> solution since if I ever decide to reconfigure my indexes, I will
> have
> dozens of coordinating code changes to perform. Also, there are
> several
> queries in which I cannot use index hints because the query requires
> column-level 'OR'-ing with which hints are incompatible.
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment from MS that this is a behavioral issue
> with
> SQL2K that needs to be addressed.
> - ITFred *
huge
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message397556.html