I have an 3rd-party application that uses a SQL back-end, but uses
Access 2000 (linked tables all using the same DSN) as an
intermediary. We recently upgraded from SQL 7 to SQL 2005, which went
fine. Now, however, we want to setup SQL Mirroring to provide
automatic failover.
The mirroring works fine. In my test environment, the mirror server
becomes active automatically when the service is stopped on the
principal server. I can see this in SQL Management Studio. WMI
events fire properly, logs are kept synchronous, and all is generally
well.
The problem is in getting Access to continue operation after the
failover. I redid the DSN to use the SQL Native Client driver instead
of the standard SQL Server driver, specified the failover partner,
made sure the default database was correct, made sure that the
specified login (using a SQL account) had the proper SPID and had its
default database configured properly. Everything looks correct. I've
redone the links using he Linked Table Manager. (The problem also
happens if I start a fresh database instead of changing the existing
one.) If I look at the properties page of each table I see this
connection string:
ODBC;DSN=<DSN Name>;DATABASE=<DB Name>;Failover_Partner=<Mirror Server
Name>;;TABLE=<Table Name>
The first time I attempt to open any table I get the following error:
"ODBC - call failed. [Microsoft][SQL Native Client]TCP Provider: An
existing connecion was forcibly closed by the remote host. (#10054)"
Subsequent attempts give me:
"ODBC - call failed. [Microsoft][SQL Native Client]Communication link
failure (#0)"
If I close and reopen the Access database, everything will work fine
again. If I leave the Access database open and alone for an
indeterminate amount of time (maybe 10-15 minutes), it will begin
working again. I don't see anything odd in the SQL logs or in Events
on the client machine or either SQL server.
I already tried disabling Shared Memory in the SQL Server
Configuration Manager, and made sure TCP/IP is the top protocol (and
Named Pipes second).
I tried adding "pooling=false" to the connection string of a linked
Access table and that failed. I added a CPTimeout value of 1 to HKLM
\Software\ODBC\ODBC.INI\<DSN Name>. Still nothing works.
Any ideas about how I can get this to work?
ODBC DSN connection fails from MS Access 2000 after SQL2005 mirrorfailover 
