I'm having a problem with SQL2005 Database mirroring.
I have an ASP application that loops for a certain amount of interations and in each iteration I create a SQL Connection object and use the failover partner in the connection string. The object then writes a simple record and then the connection is closed and the process starts again.
About half way through my loop I force a failover to the server mentioned as the failover partner in the connection string. At this point my application encounters a SQL Exception error and the application fails.
I read in this article, http://www.hide-link.com/ , that this is expected and that you shoud request a new connection using the same connection string but this isn't working for me and unfortunately no examples are given anywhere.
I would appreciate any help. Thanks
Luis Bonilla
Here is a sample of my code:
Dim constring As String = "Server=SVR01.x.x.com\InstanceName;Failover Partner=SVR02.x.x.com\InstanceName;" _ & "Database=test;" _ & "Integrated Security=SSPI;"
Do Until k = 30 Using con As New SqlConnection(constring)
Dim cmd As New SqlCommand()
Try con.Open() '<------This is where the SQL Exception error is occurring Catch ex As SqlException Dim en As String = ex.ErrorCode Dim em As String = ex.Message Dim emm As String = ex.Number Dim enn As Integer = ex.State con.Dispose() 'Attempt to cleanup and reinitiate connection con.ConnectionString = constring 'Attempt to cleanup and reinitiate connection con.Open() 'Attempt to cleanup and reinitiate connection End Try
cmd.Connection = con cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter" & CStr(k) & "', 'Test')" 'For testing
Try cmd.ExecuteNonQuery() Catch lblresult.Text = "Results = Error!!!" GoTo errhandler End Try
lblresult.Text = "Database responded successfully"
con.Close()
GoTo cont errhandler: con.Close() con.Dispose()
cont: End Using Sleep(1000) 'For testing k = k + 1 'For testing
.NET Development16
|