SQL command:
update [Ascertain_PROD].dbo.HostStatus
set Comments = 'Decommissioned' where HostID in (SELECT H.[HostID]
FROM [Ascertain_PROD].[dbo].[Host] H left join [Ascertain_PROD].dbo.HostStatus HS on H.HostID = HS.HostID
where h.HostName = 'XXXXXServerNameXXXXX')
We need to create a powershell script using above update sql command to run remotely to update the database . Once run the script, server will be updated as decommissioned in our inventory and it should run using credentails. please help me to get the script to update.
Example:
[string]$sqlserver = "winsqla13.contonso.com"
[string]$sqllogin = "username"
[string]$sqlpwd = "password"
[string] $Database = "Ascertain"
[string] $UserSqlQuery= $("update [Ascertain_PROD].dbo.HostStatus
set Comments = 'Decommissioned' where HostID in (SELECT H.[HostID]
FROM [Ascertain_PROD].[dbo].[Host] H left join [Ascertain_PROD].dbo.HostStatus HS on H.HostID = HS.HostID
where h.HostName = 'XXXXXServerNameXXXXX')
")
# declaration not necessary, but good practice
$resultsDataTable = New-Object System.Data.DataTable
$resultsDataTable = ExecuteSqlQuery $sqlserver $Database $UserSqlQuery
# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$sqlserver';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
$Connection.Close()
return $Datatable
}
#validate we got data
Write-Host ("The table contains: " + $resultsDataTable.Rows.Count + " rows")
