VB Forum / Database Access / October 2005

Thanks for getting back with me.

Here goes:

Imports System.Data.SqlClient
Imports System.ServiceProcess

Public Class BaseDataCollector
#Region " Private Member variables "
   Dim AppSettings As New System.Configuration.AppSettingsReader
   Friend WithEvents connRemoteServer As System.Data.SqlClient.SqlConnection
   Friend WithEvents connCentralServer As New
System.Data.SqlClient.SqlConnection
   Dim EL As System.Diagnostics.EventLog

#End Region
   
   Public Sub CollectServerAddresses()
       Try
           Dim i As Integer = 0
           Dim a_IPAddresses(i) As String
           Dim drIPAddresses As SqlDataReader
           Dim cmdIPAddress As SqlCommand

           ' If there are no IP addresses available in the cache then
retrieve them from LW_CP_Servers
           ' If a_IPAddresses.Length = -1 Then
           connCentralServer.ConnectionString = "Persist Security
Info=True;Initial Catalog=MonServer;Data Source=192.168.4.101;Packet
Size=4096;Connection Timeout=120; user id = Cockpit_development; password =
P@ssw0rd;"
           Console.WriteLine(connCentralServer.ConnectionString)
           connCentralServer.Open()
           cmdIPAddress = New SqlCommand("Select Server_URL from
SERVERS_LIST", connCentralServer)
           drIPAddresses = cmdIPAddress.ExecuteReader

           Do While drIPAddresses.Read()
               a_IPAddresses(i) = (drIPAddresses.GetString(0).ToString())
               Console.WriteLine(a_IPAddresses.GetValue(i))
               'Console.WriteLine(i)
               'Console.WriteLine(drIPAddresses.GetString(0).ToString())
               i = i + 1
               ReDim Preserve a_IPAddresses(i)
           Loop

           drIPAddresses.Close()
           connCentralServer.Close()
           'End If

           For i = 0 To a_IPAddresses.Length - 1
               ' Collect all monitored bases
               CollectRemoteServerData(a_IPAddresses(i))
           Next
       Catch ex As Exception
           Console.WriteLine(ex.Message)
       End Try

   End Sub

   Public Sub CollectRemoteServerData(ByVal s_IP As String)
       Try

           Dim RetrievedDatapts As New DataSet
           connRemoteServer = New SqlClient.SqlConnection
           connRemoteServer.ConnectionString = "Persist Security
Info=True;Initial Catalog=remData ;Packet Size=4096;Connection
Timeout=120;Integrated Security=SSPI;Data Source=" + s_IP + ";"
           Console.WriteLine(connRemoteServer.ConnectionString)
           Console.WriteLine(connRemoteServer.State)
           Console.WriteLine(connRemoteServer.DataSource)
           'Console.WriteLine(connRemoteServer.ServerVersion)
           Console.WriteLine(connRemoteServer.Site)
           Console.WriteLine(connRemoteServer.ToString)
           connRemoteServer.Open()

           'SqlHelper.ExecuteNonQuery(connRemoteServer.ConnectionString,
CommandType.StoredProcedure, "datapts_prep_for_transmission")

           ' RetrievedDatapts =
SqlHelper.ExecuteDataset(connRemoteServer.ConnectionString,
CommandType.StoredProcedure, "DATAPT_LISTS_SEL")
           ' EL.WriteEntry("Successful Update")
           'connRemoteServer.ConnectionString = ""
           connRemoteServer.Close()
           connRemoteServer = Nothing
       Catch e As Exception
           EL.WriteEntry("Update Failed" + vbCrLf + e.Message)

       End Try

   End Sub

End Class