472,143 Members | 1,833 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

ODBC DSN connection fails from MS Access 2000 after SQL2005 mirrorfailover

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

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?
Aug 28 '08 #1
1 5282
I responded to your duplicated question here:

-Eric Isaacs
Aug 29 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Scott A. Jones | last post: by
reply views Thread by Saiars | last post: by

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.