[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Alternative SQL server

Is there a way to dynamically contact an alternative sql server should the main not be contactable
ie alternate sql cinnection string


cheers

davoman
0
davoman
Asked:
davoman
  • 2
1 Solution
 
RonaldBiemansCommented:
Yes, try something like this

Protected Const SQL_CONNECTION1_STRING As String = _
        "Server=SQLserver1;" & _
        "DataBase=northwind;" & _
        "Integrated Security=SSPI"

    Protected Const SQL_CONNECTION2_STRING As String = _
        "Server=SQLSERVER2;" & _
        "DataBase=northwind;" & _
        "Integrated Security=SSPI"
Protected DidPreviouslyConnect As Boolean = False

private sub yoursub(etc...)

Dim isConnecting As Boolean = True
        While isConnecting
            Try
                Dim cnnNW As New SqlConnection(connectionString)

' do your stuff here

                isConnecting = False
                DidPreviouslyConnect = True
                frmStatusMessage.Close()

            Catch exSql As SqlException
                MsgBox(exSql.Message, MsgBoxStyle.Critical, Me.Text)
                Exit Sub

            Catch ex As Exception
                If connectionString = SQL_CONNECTION1_STRING Then
                    ' Couldn't connect to first SQL Server.  Now try second
                    connectionString = SQL_CONNECTION2_STRING
                Else
                    ' Unable to connect to SQL Server 1 or 2, so exit app.
                    MsgBox("unable to connect to either sql servers", MsgBoxStyle.Critical, Me.Text)
                    End
                End If
            End Try
        End While

end sub
0
 
RonaldBiemansCommented:
sorry forgot 1 declaration


Protected Const SQL_CONNECTION1_STRING As String = _
        "Server=SQLserver1;" & _
        "DataBase=northwind;" & _
        "Integrated Security=SSPI"

    Protected Const SQL_CONNECTION2_STRING As String = _
        "Server=SQLSERVER2;" & _
        "DataBase=northwind;" & _
        "Integrated Security=SSPI"

Protected DidPreviouslyConnect As Boolean = False
Protected connectionString As String = SQL_CONNECTION1_STRING

private sub yoursub(etc...)

Dim isConnecting As Boolean = True
        While isConnecting
            Try
                Dim cnnNW As New SqlConnection(connectionString)

' do your stuff here

                isConnecting = False
                DidPreviouslyConnect = True
                frmStatusMessage.Close()

            Catch exSql As SqlException
                MsgBox(exSql.Message, MsgBoxStyle.Critical, Me.Text)
                Exit Sub

            Catch ex As Exception
                If connectionString = SQL_CONNECTION1_STRING Then
                    ' Couldn't connect to first SQL Server.  Now try second
                    connectionString = SQL_CONNECTION2_STRING
                Else
                    ' Unable to connect to SQL Server 1 or 2, so exit app.
                    MsgBox("unable to connect to either sql servers", MsgBoxStyle.Critical, Me.Text)
                    End
                End If
            End Try
        End While

end sub
0
 
davomanAuthor Commented:
i see thats cool cheers
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now