Pass-through queries fail even after SQL Server reconnected

[marshmallowww]

I have an Access 2000 mde application which uses ADO and pass through
queries to communicate with SQL Server 7, 2000 or 2005.

Some of my customers, especially those with SQL Server 2005, have had
pass-through queries fail due to intermittent connection failures.

I can easily restablish a connection for ADO.

My problem is with pass-through queries.

It appears from my experiments that MSAccess establishes a connection
to the SQL Server the first time it runs a pass-through query. It then
tries to use that same connection whenever any later pass-through
queries are run.

If there is a network problem or the SQL Server temporarily goes
offline or the SQL Server rejects the connection for whatever reason,
any later attempts to use pass-through queries fail, even after the
SQL Server is back online and connected via ADO.

The first one to fail returns the following error message:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite
(WrapperWrite()). (#55) [Microsoft][ODBC SQL Server Driver][Shared
Memory]General Network Error. Check your network documentation. (#11)

Subsequent failures return:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Communication link failure (#0)

Obviously these errors are telling me to fix something in the network
or SQL Server.

Unfortunately the networks and SQL Servers where my application is
deployed are not under my control.

I just want to accept the fact the connection has been interrupted and
establish a fresh connection.

I have tried using DAO to create a querydef object from the query and
reset its connection string. The code runs but the queries still fail.

Likewise I have tried using ADOX to reset the Pass Through Query
Connect String property of the command of the relevant procedure in
the ADOX catalog . The code runs but the queries still fail.

I tried closing and reopening the application without closing Access.
The queries still fail.

If I close Access altogether then reopen it and the application, the
queries work again.

Also, if leave the application open and wait a long time (e.g. half an
hour), the queries work again.

I don't want my users to have to wait half an hour or close and reopen
Access.

Is there some property I can set or method I can run on the Access
instance that will allow my pass-through queries to run again once the
SQL Server is back online?

I have tried setting all the various cryptically-named intervals that
appear in the Access 2000 Tools/Options/Advanced dialog to low
numbers. It makes no difference.

Does anyone have any suggestions?

Mike

[Tom van Stiphout]

On Wed, 30 Apr 2008 17:49:37 -0700 (PDT), ma***********@yahoo.com
wrote:

In my experience "General Network Error" is a very serious event and
indicates (perhaps intermittent) network connectivity problems. Think
of the entire chain between the client machine and the server machine:
the cables, the nic, the switch, some part of it is intermittently
failing and until it is identified and fixed there is little software
can do.

ODBC is a pretty old way to connect to SQL Server. I think no
development has been going on there for many years. At least consider
upgrading to OLEDB. Of course the real development is with ADO.NET,
but then you're leaving the realms of Access.

-Tom.

>I have an Access 2000 mde application which uses ADO and pass through
queries to communicate with SQL Server 7, 2000 or 2005.

Some of my customers, especially those with SQL Server 2005, have had
pass-through queries fail due to intermittent connection failures.

I can easily restablish a connection for ADO.

My problem is with pass-through queries.

It appears from my experiments that MSAccess establishes a connection
to the SQL Server the first time it runs a pass-through query. It then
tries to use that same connection whenever any later pass-through
queries are run.

If there is a network problem or the SQL Server temporarily goes
offline or the SQL Server rejects the connection for whatever reason,
any later attempts to use pass-through queries fail, even after the
SQL Server is back online and connected via ADO.

The first one to fail returns the following error message:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite
(WrapperWrite()). (#55) [Microsoft][ODBC SQL Server Driver][Shared
Memory]General Network Error. Check your network documentation. (#11)

Subsequent failures return:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Communication link failure (#0)

Obviously these errors are telling me to fix something in the network
or SQL Server.

Unfortunately the networks and SQL Servers where my application is
deployed are not under my control.

I just want to accept the fact the connection has been interrupted and
establish a fresh connection.

I have tried using DAO to create a querydef object from the query and
reset its connection string. The code runs but the queries still fail.

Likewise I have tried using ADOX to reset the Pass Through Query
Connect String property of the command of the relevant procedure in
the ADOX catalog . The code runs but the queries still fail.

I tried closing and reopening the application without closing Access.
The queries still fail.

If I close Access altogether then reopen it and the application, the
queries work again.

Also, if leave the application open and wait a long time (e.g. half an
hour), the queries work again.

I don't want my users to have to wait half an hour or close and reopen
Access.

Is there some property I can set or method I can run on the Access
instance that will allow my pass-through queries to run again once the
SQL Server is back online?

I have tried setting all the various cryptically-named intervals that
appear in the Access 2000 Tools/Options/Advanced dialog to low
numbers. It makes no difference.

Does anyone have any suggestions?

Mike

[marshmallowww]

Thanks Tom,

This is a big old application and I'm stuck with pass-through queries.

I'm also not in a position to exert much influence on the IT
departments of our numerous customers. I suspect that the real culprit
is SQL Server 2005 closing connections that are still needed. Our SQL
Server 7 and 2000 customers have not reported this problem.

Ideally I'd like to find a solution that lets my app reconnect
whatever the cause of the lost connection.

Does anyone have any other ideas, especially on how to tell Access to
renew/replace the connection it uses for pass-through queries?

Mike
On May 1, 2:12*pm, Tom van Stiphout <[e-mail]:

On Wed, 30 Apr 2008 17:49:37 -0700 (PDT), [e-mail]
wrote:

In my experience "General Network Error" is a very serious event and
indicates (perhaps intermittent) network connectivity problems. Think
of the entire chain between the client machine and the server machine:
the cables, the nic, the switch, some part of it is intermittently
failing and until it is identified and fixed there is little software
can do.

ODBC is a pretty old way to connect to SQL Server. I think no
development has been going on there for many years. At least consider
upgrading to OLEDB. Of course the real development is with ADO.NET,
but then you're leaving the realms of Access.

-Tom.

I have an Access 2000 mde application which uses ADO and pass through
queries to communicate with SQL Server 7, 2000 or 2005.

Some of my customers, especially those with SQL Server 2005, have had
pass-through queries fail due to intermittent connection failures.

I can easily restablish a connection for ADO.

My problem is with pass-through queries.

It appears from my experiments that MSAccess establishes a connection
to the SQL Server the first time it runs a pass-through query. It then
tries to use that same connection whenever any later pass-through
queries are run.

If there is a network problem or the SQL Server temporarily goes
offline or the SQL Server rejects the connection for whatever reason,
any later attempts to use pass-through queries fail, even after the
SQL Server is back online and connected via ADO.

The first one to fail returns the following error message:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite
(WrapperWrite()). (#55) [Microsoft][ODBC SQL Server Driver][Shared
Memory]General Network Error. Check your network documentation. (#11)

Subsequent failures return:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Communication link failure (#0)

Obviously these errors are telling me to fix something in the network
or SQL Server.

Unfortunately the networks and SQL Servers where my application is
deployed are not under my control.

I just want to accept the fact the connection has been interrupted and
establish a fresh connection.

I have tried using DAO to create a querydef object from the query and
reset its connection string. The code runs but the queries still fail.

Likewise I have tried using ADOX to reset the Pass Through Query
Connect String property of the command of the relevant procedure in
the ADOX catalog . The code runs but the queries still fail.

I tried closing and reopening the application without closing Access.
The queries still fail.

If I close Access altogether then reopen it and the application, the
queries work again.

Also, if leave the application open and wait a long time (e.g. half an
hour), the queries work again.

I don't want my users to have to wait half an hour or close and reopen
Access.

Is there some property I can set or method I can run on the Access
instance that will allow my pass-through queries to run again once the
SQL Server is back online?

I have tried setting all the various cryptically-named intervals that
appear in the Access 2000 Tools/Options/Advanced dialog to low
numbers. It makes no difference.

Does anyone have any suggestions?

Mike- Hide quoted text -

- Show quoted text -

[Rich P]

Greetings,

As Tom pointed out, ODBC is old technology, and my guess is that Sql
Server 2005 is trying to migrate away from ODBC. The problem with ODBC
is that it is a continuous connection where ADO connects and disconnects
for each call. Most likely, these ODBC connection failures are really
"Deadlocking" issues. Not only does Access not deal with deadlocking -
it does not give a very informative error message.

Microsoft went all out to resolve the deadlocking problems with the
development of .Net (ADO.Net), and they did a real nice job. For a
multi user environment - deadlocking is always a major issue. With
ADO.Net, when multiple users hit the same table/record at the same time
- ADO.Net will set up a sort of qeue and gives a message (not too pretty
of a message - something about Transaction has been put on a qeue - but
the word "Failure" is not included in the message) -- you just click OK,
and your transaction gets processed next.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On Thu, 1 May 2008 02:48:12 -0700 (PDT), ma***********@yahoo.com
wrote:

I'm sorry the news isn't any better. Here is a possible solution:
perhaps we are timing out, and if that's so, maybe it would help if we
ran a very quick query (select * from EmptyTable where ID=-1) based on
a 1-minute timer.

Another far-fetched possibility: perhaps you could experiment with
workspace.IsolateODBCTrans.

Did you see this comment in the OpenRecordset documentation: You
should also use the dbSeeChanges constant if you open a Recordset in a
Microsoft Access database engine-connected ODBC workspace against a
Microsoft SQL Server 6.0 (or later) table that has an IDENTITY column,
otherwise an error may result.

Can you post a simple example of the source code you're using?

-Tom.

>Thanks Tom,

This is a big old application and I'm stuck with pass-through queries.

I'm also not in a position to exert much influence on the IT
departments of our numerous customers. I suspect that the real culprit
is SQL Server 2005 closing connections that are still needed. Our SQL
Server 7 and 2000 customers have not reported this problem.

Ideally I'd like to find a solution that lets my app reconnect
whatever the cause of the lost connection.

Does anyone have any other ideas, especially on how to tell Access to
renew/replace the connection it uses for pass-through queries?

Mike

<clip>

[marshmallowww]

Thanks Tom,

Sending a quick query every minute is a good idea. In fact I've had
that idea too and have already built it into the next version of our
application. I'm hoping it will prevent many of the losses of
connection.

But I was also hoping for a more comprehensive solution in case of
loss of connection for any other reason.

I tried setting workspace.IsolateODBCTrans to true but it made no
difference.

Re your dbSeeChanges suggestion, unless I've misunderstood,the problem
is not related to recordsets as such. Only to pass-through queries.

Posting code would be a bit beside the point. Although the querires
are nomally called from code, the problem still manifests itself
without any code running.

Thanks for trying.

I think my next move will be to try Microsoft's own Access newsgroup
in case the authors of the problem are lurking there.

Mike

Mike