connection to remote server
#server IP: 176.57.189.102
#server: VMI690251
$remote_SQLServer = "41.211.48.78\edcu-lab"
$local_SQLServer = "176.57.189.102\VMI690251"
$SQLDBName = "Sentinel Surveillance"
$SQLUser = "sentinel"
$SQLPassword = "P@ssw0rd"
# fetch data from remote server
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $remote_SQLServer; Database = $SQLDBName; User ID= $SQLUser; Password= $SQLPassword"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ” SELECT * FROM [COVID_Screning]”
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
# Assign the Sql Command object Sql Adapter object
$SqlAdapter.SelectCommand = $SqlCmd
# Declare DataSet object
$DataSet = New-Object System.Data.DataSet
# Execute Sql Adapter object to fill the DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0]
$SqlConnection.Close()
#----------------------------------------------------------------
#updating the local server with fetched data
#$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
#$SqlConnection.ConnectionString = "Server = $local_SQLServer; Database = $SQLDBName; User ID= $SQLUser; Password= $SQLPassword"
#$SqlConnection.Open()
#$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
#$sqlQuery = "BULK INSERT [Sentinel Surveillance].dbo.[COVID_Screening] FROM $DataSet.Tables[0]"
#$SqlCmd.CommandText = $sqlQuery
#$SqlCmd.Connection = $SqlConnection
#$sqlCmd.EndExecuteNonQuery()
#$Connection.Close()