SQL Server 2005 Linked Server and Mirroring issue

Hi,

I have a DB 'NCAccounts' which is mirrored (Principal and Mirror in two separate servers).  I have yet another DB in a 3rd server.  In the 3rd server, I created a Linked Server as follows to connect to 'NCAccounts':
---------------------------------------------------------------
--Create Linked Server
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',
@srvproduct=N'.', @provider=N'SQLNCLI',
@provstr=N'Server=DBASE-1;Partner=DBASE-2',
@catalog=N'NCAccounts'

--Create login for linked server
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'MyLinkedServer',@useself=N'False',@locallogin=N'Admin',@rmtuser=N'Admin',@rmtpassword=N'adminpwd'
---------------------------------------------------------------

The linked server was created, but on executing
"select * from MyLinkedServer.master.dbo.sysdatabases", the following error message was displayed:

---------------------------------------------------------------
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'DBASE-1\admin'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "NCAccounts" requested by the login. The login failed.
OLE DB provider "SQLNCLI" for linked server "MyLinkedServer" returned message "Invalid connection string attribute".
---------------------------------------------------------------

At this point I noted that DBASE-1 was acting as the Mirror and DBASE-2 was acting as the Principal in the Mirror setup.  So I recreated the Linked Server by interchanging the servers as:
---------------------------------------------------------------
@provstr=N'Server=DBASE-2;Partner=DBASE-1',
---------------------------------------------------------------
and it worked! (Please note that DBASE-1 and DBASE-2 have been interchanged here)

QUESTION: Won't Linked Server automatically consider and switch between the Primary and Partner (Mirror) DBs in the wake of a failover?

Thank you in advance for any replies.
ARSSEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you are in the situation where the instance hosting DBASE-2 was up, but DBASE-2 itself not available... at least, that would explain the first error ...

the connection string might be changed slightly: "Failover Partner" instead of "Partner"
http://www.connectionstrings.com/sql-server-2005

apart from that: what exact version are you running?
ARSSEAuthor Commented:
Hi, thank you very much for pointing out in the right direction, a small amendment in the connection string ...
It needs to be "FailoverPartner" and NOT "Failover Partner" ... and then All Is Well :)

Sample:
--Create Linked Server
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',
@srvproduct=N'.', @provider=N'SQLNCLI',
@provstr=N'Server=DBASE-1;FailoverPartner=DBASE-2',
@catalog=N'NCAccounts'


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ARSSEAuthor Commented:
Took up on the hint provided in the previous post and came up with the solution for my problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.