Failover Partner issue
-
Dear all,
I've a VB application connected to database called ITOGC01 on a SQL2005 std edition server called ITDB001.
I've installed a second SQL2005 std edition in another server called ITDB002 () and a third one called ITwitness01
I've created a mirroring structure and tested with the Failover Test button. Everything working absolutely fine. My application get disconnected but reconnecting it, works perfectly on ITDB002.
I've changed the connection string in the application to support the mirroring:
"Provider=SQLNCLI;Data Source=ITDB001;Failover Partner=ITDB002;Initial Catalog=ITOGC01;User ID=myself;Password=myself;DataTypeCompatibility=80"
I've tried to turn off server ITDB001 where the principal database is located. ITDB002 become Principal and, as I expected, my application got disconnected. Reconnecting it anyway, I'm not able to query ITDB002 anymore.
I've created a simple VBscript:
Main
Sub Main
Dim moConn
Dim moRS
Dim sCvar
On Error Resume next
Set moConn = CreateObject("ADODB.Connection")
moConn.CursorLocation = 3
moConn.Provider="SQLNCLI"
Err.Clear
sCvar = "Provider=SQLNCLI;Data Source=ITDB001;Failover Partner=ITDB002;Initial Catalog=ITOGC01;User ID=myself;Password=myself;DataTypeCompatibility=80"
moConn.Open sCvar
If Err.Number <> 0 Then
MsgBox err.description
Else
MsgBox "connected"
End If
moConn.Close
Set moConn = Nothing
End Sub
to test connectivity and it confirm to me that, if I'm just using the Failover button, everything is working fine, but if I'm turning off the ITDB001 where principal database is located, connection is lost.
Does anyone have a solution to this issue^
Thank you in advance
Marco
-
Not sure if this is the problem, but shouldn't it be DataTypeCompatibility=90 ?
Markus
[font="Verdana"]Markus Bohse[/font]
-
Hi Markus,
Apologies for the dealy. It seems to work. Thank you very much.
Marco
-
I've got the final result. It doesn't work. Error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value. if available. No work was done
I've tried to change my script into:
Main
Sub Main
Dim moConn
Dim moRS
Dim sCvar
On Error Resume next
Set moConn = CreateObject("ADODB.Connection")
moConn.CursorLocation = 3
moConn.Provider="SQLNCLI.1"
Err.Clear
sCvar = "Provider=SQLNCLI.1;Data Source=ITDB003;Failover Partner=ITDB005;Initial Catalog=ITOGC01;Persist Security Info=True;User ID=CCLASMGR_ITOGC01;Password=CCLASMGR;Connect Timeout=5;DataTypeCompatibility=80"
moConn.Open sCvar
If Err.Number <> 0 Then
MsgBox err.description
Else
MsgBox "connected"
End If
moConn.Close
Set moConn = Nothing
End Sub
-------------------------
or
-------------------------
Main
Sub Main
Dim moConn
Dim moRS
Dim sCvar
On Error Resume next
Set moConn = CreateObject("ADODB.Connection")
moConn.CursorLocation = 3
moConn.Provider="SQLNCLI"
Err.Clear
sCvar = "Provider=SQLNCLI;Data Source=ITDB003;Failover Partner=ITDB005;Initial Catalog=ITOGC01;Persist Security Info=True;User ID=CCLASMGR_ITOGC01;Password=CCLASMGR;Connect Timeout=5;DataTypeCompatibility=90"
moConn.Open sCvar
If Err.Number <> 0 Then
MsgBox err.description
Else
MsgBox "connected"
End If
moConn.Close
Set moConn = Nothing
End Sub
-------------------------
or
-------------------------
Main
Sub Main
Dim moConn
Dim moRS
Dim sCvar
On Error Resume next
Set moConn = CreateObject("ADODB.Connection")
moConn.CursorLocation = 3
moConn.Provider="SQLNCLI.1"
Err.Clear
sCvar = "Provider=SQLNCLI.1;Data Source=ITDB003;Failover Partner=ITDB005;Initial Catalog=ITOGC01;Persist Security Info=True;User ID=CCLASMGR_ITOGC01;Password=CCLASMGR;Connect Timeout=5;DataTypeCompatibility=90"
moConn.Open sCvar
If Err.Number <> 0 Then
MsgBox err.description
Else
MsgBox "connected"
End If
moConn.Close
Set moConn = Nothing
End Sub
But I still got the same error messag. I don't really know how to make it work this Failover in a VBS application.
Does anyone have an idea?
Thank you in advance
-
Does anyone know the bloody solution to this stupid issue?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply