Re: Pass-through queries fail even after SQL Server reconnected

On Wed, 30 Apr 2008 17:49:37 -0700 (PDT), marshmallowww@xxxxxxxxx
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
.

Relevant Pages

  • Re: Connection from remote computer to network SQL Server
    ... There is no firewall on the W2K machine acting as the SQL server. ... I tried making the SQL machine a "trusted" on the router. ... connection works. ... To find the IP address of your computer inside the network, ...
    (microsoft.public.access.adp.sqlserver)
  • Cant connect to SQL Server from Novell workstation
    ... My customer is trying to get my VB.net program to access SQL Server 2000 ... over a Novell network and they're getting a connection error. ...
    (microsoft.public.sqlserver.connect)
  • RE: ASP .Net connection to Sql Server
    ... Using "dbmssocn" tells it to use the TCP/IP network driver, ... It is connecting via a connection string similiar to: ... > string to our production SQL server and everything runs fine. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: java.sql.SQLException (Error establishing socket)
    ... | Error: No active Connection ... Since you are making connections to a remote SQL Server via the Internet, ... If the network is fine, ... Please reply only to the newsgroups. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Temporary table disappears
    ... John ... > You don't give the version of SQL server, but at a guess you are seeing some ... Check your network documentation. ... And all other nine queries are ...
    (microsoft.public.sqlserver.connect)