Sunday, March 11, 2012

Connecting to Failover Partner using ODBC and OLE DB


I need to connect to mirrored SQL servers (Developer Edition) using OLE DB, I tried both OLE DB and ODBC, but it doesn't work
I used connection ODBC string:
Driver={SQL Native Client};Server=10.0.1.161;Failover Partner=10.0.1.162;Uid=test;Pwd=test;Database=TestDB
if server 161 is principal and server 162 mirror, it connects ok, but when I exchange server roles, connect fails (the error message is: Cannot open database "TestDB" requested by the login. The login failed. in LOGIN)
the connect string using OLE DB is:
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=test;Password=test;Failover Partner=10.0.1.162;Initial Catalog=TestDB;Data Source=10.0.1.161;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False
error message is the same
when I try to connect using VS 2005 using connection string Database=TestDB;User Id=test;Password=test;Server=10.0.1.161;Failover Partner=10.0.1.162, it works OK
i have installed SQL server 2005 (on local - client machine) with SQL Native Client and also
SQL Server service pack 1
Is there any way how to connect from OLE DB?
Thanks

With OLE DB the connection string keyword is 'FailoverPartner' (no space) in the provider string for IDBInitialize::Initialize and 'Failover Partner' (one space) for IDataInitialize::GetDataSource.

With ADO it's 'Failover Partner' (one space) and ODBC it's 'Failover_Partner'

Depending on the method you use to connect, you may be using the wrong keyword.

Mirroring happens per database rather than per server, and covers data (tables and indexes) but not user accounts and access rights. These have to be set up in both databases for failover to work, otherwise you can have a login fail after the servers switch roles if the login does not have access rights in the mirror database.

No comments:

Post a Comment