473,893 Members | 1,878 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
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=<T able 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=fa lse" 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 5412
I responded to your duplicated question here:
http://groups.google.com/group/micro...bbc0443321257#

-Eric Isaacs
Aug 29 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3920
by: Richard Muller | last post by:
Hi All, I've got the ASP script shown below that complains as follows: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Data source name not found and no default driver specified /ShowEvents.asp, line 24 Line 24 of the script is the line:
6
6795
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
6
3385
by: Anil Kumar Saharan | last post by:
Hi, I have a loopback connection using ODBC in the DLL initialization code of the SQL Server ESP Module (SQL Server 2000). The loopback connection works fine when the DSN is specifed with the "NT Authentication", however the same fails when specified with the "SQL Server user authentication". I have tried
3
25076
by: Dan | last post by:
Hi, I have an access 2003 database which connect to sql server via a DSN that goes through a VPN. The database works fine and everyone can coonect from their normal computers but for anyone working on their Laptops (IBM Think Pad T42's) they cannnot connect to the datasource. The DSN fails upon connection attempt with : Connection Failed: SQLState: '01000' SQL Server Error: 1326 ConnectionOpen
3
4382
by: DFS | last post by:
This code fails to update the connection strings (Access97 - SQL Server 2000 table links). It works in Access2000. Public Sub updateConnStrings() For i = 0 To db.TableDefs.Count - 1 Set td = db.TableDefs(i) td.Connect = "ODBC;DSN=dsnName;UID=DFS;PWD=DFSpw" td.RefreshLink Next i db.TableDefs.Refresh
2
17649
by: Scott A. Jones | last post by:
Hello, I am in the process of 'figuring out' how to verify if I have a valid connection to an ODBC data source (SQL Server 2000). I've written code to run a pass-through query against a table that is in the database. If the query fails, I want the function to return a 'FALSE'. The code works fine if the connection exists (returns 'TRUE'). However, if the connection is invalid, I am forced to deal with an ODBC login dialog box that I...
1
3806
by: Paul | last post by:
Hello, I am converting an Access database on our network to a sql 2000 backend and keeping access as the front end. The access database has evolved and been a solution to collect data but now we want to move the tables over to sql 2000. We have successfully done that but are now having difficulties with an ODBC connection. Initiall linking the tables is not an issue and I am able to connect with no problem. However after closing down...
0
1564
by: Hongbo | last post by:
Hi, I have a test server with Windows 2003 Standard Server. SQL Server 2000 Standard Edition was installed earlier as default instance. My ASP.Net web site works fine with the connection string "Server=localhost;UID=sa;PWD=sa;Database=dbSales" under such circumstance. Then I installed SQL Server 2005 Standard Edition on the very same machine with a instance name "sql2005" and "sa2005" as password to "sa" account.
3
2543
by: KEMoore | last post by:
I am trying to read data located on an ODBC box. I have multiple queries built in SQL2005. Now I just need to be able to access this data from the ODBC server in the SQL2005. I have tried to create a linked sever and failed miserably. I have an ODBC connection set up on my machine with the following settings: Data Source Name: MSXXXP1 Description: Client data source Server Name: tcp:XXX.XXX.XXX.XXX.XXXX ServerDSN: MSXXXP1
0
9988
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11247
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10841
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10469
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9648
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8025
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5860
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6066
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4684
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.