ADO.NET - Transparent Client Redirect (not working?)

[Ian Jenkins, MCSD]

I have a database setup using SQL Server Mirroring. My database
servers are running SQL Server 2005 SP1, Enterprise Edition.

I have created a windows application that will insert a record into my
mirrored database by clicking a button. (connection string below)

If I run my test application I can successfully insert a record into
the database as expected. However, if I take down the principal (the
mirror becomes the principal) and if I try to insert another record it
does not succeed. I get a SQL timeout exception. If I close the
application and restart it I can successfully insert a new record
(where the old mirror is now principal). What do I need to do to be
able to not close the application to take advantage of the "transparent
client redirect" that ADO.NET boasts about?

SqlConnection.ConnectionString:
Server=sql1; Failover Partner=sql2; Database=TestMirror;
Network=dbmssocn; uid=sa; pwd=mypass

Thanks,
- Ian

 

[Ian Jenkins, MCSD]

I found the answer to my own question.

See this KB article:
http://support.microsoft.com/kb/912151

If you don't obtain the hotfix and wait for the service pack you can
switch your code to use use System.Data.OleDb instead of
System.Data.SqlClient.

Make sure your connection string looks similar to this:
"Provider=SQLNCLI;Server=sql1; Failover Partner=sql2;
Database=TestMirror; Network=dbmssocn; uid=sa; pwd=mypass"

Hope this helps!

Thanks,
Ian